SQL format module
Project target create a simple module support dynamic inject variable to sql statement and batch execute SQL statement
Table of contents
Installation
pip3 install sqlformatDevelopment
Project is using pipenv to manage dependency
So in order to get started, install pipenv first.
pip3 install pipenv
# Or if you are mac user
brew install pipenvAfter install pipenv, you can setup this repo by following command
pipenv --three installTesting
Following command will execute all test and shown the code coverage report
pipenv shell
py.test --cov=sqlformatTerm
Procedure
A json array contain list file that contain sql statement to be execute
Example: Sample procedure
Statement variable
A place holder to used for inject value on run-time, it using Standard python string format.
So any feature supported by "".format() will worked on statement variable
Example: Sample statement with variable
Usage
Stored on local file system
from sqlformat import SQLBatch
from sqlformat import LocalStorageStrategy
from sqlformat import ResultBufferStrategy
sql_batch = SQLBatch(LocalStorageStrategy(), ResultBufferStrategy())
sql_batch.result_persistence('/tmp/hello.txt')
sql_batch.add_procedure('./tests/fixture/procedure/sample.json', {
"keyword": {
"foo": "10",
"bar": "20"
}
})
statements = sql_batch.get_statements() # Will contain both hello01 & hello02.sql
print(statements) #Statement contain {foo}, {bar} will replace to 10 & 20Stored on AWS S3
import boto3
from sqlformat import SQLBatch
from sqlformat import AWSStorageStrategy
from sqlformat import ResultBufferStrategy
s3_client = boto3.resource('s3')
sql_batch = SQLBatch(AWSStorageStrategy(s3_client), ResultBufferStrategy())
sql_batch.result_persistence('/tmp/hello.txt')
sql_batch.add_procedure('s3://dng-misc/procedure/sample.json', {
"keyword": {
"foo": "10",
"bar": "20"
}
})
statements = sql_batch.get_statements() # Will contain both hello01 & hello02.sql
print(statements) #Statement contain {foo}, {bar} will replace to 10 & 20Best Practice
- Use "" as prefix and "" as suffix of variable name if variable suppose is injected by program rather then user input.
Example: "temp_table"
