Usage Examples

SelectBuilder

import

In [47]:

from sqlstring.sql_builder import SelectBuilder

Basic Examples

from_table()

In [48]:

builder = SelectBuilder()
builder.from_table('address').get_query_string()

Out[48]:

'SELECT * FROM address;'

distinct()

In [49]:

builder = SelectBuilder()
builder.from_table('address').distinct().get_query_string()

Out[49]:

'SELECT DISTINCT * FROM address;'

column()

In [50]:

builder = SelectBuilder()
builder.from_table('address').column(['city', 'state_code'])
builder.get_query_string()

Out[50]:

'SELECT city, state_code FROM address;'

where()

In [51]:

builder = SelectBuilder()
builder.from_table('address').where('state_code', '=', " 'CA' ")
builder.where('city', '=', " 'Oakland' ", 'AND')
builder.get_query_string()

Out[51]:

"SELECT * FROM address WHERE state_code = 'CA' AND city = 'Oakland';"

group_by()

In [52]:

builder = SelectBuilder()
builder.from_table('address').group_by(['state_code', 'city'])
builder.get_query_string()

Out[52]:

'SELECT * FROM address GROUP BY state_code, city;'

order_by()

In [53]:

builder = SelectBuilder()
builder.from_table('address').order_by(['state_code', 'city'], 'DESC')
builder.get_query_string()

Out[53]:

'SELECT * FROM address ORDER BY state_code DESC, city DESC;'

limit() and offset()

In [54]:

builder = SelectBuilder()
builder.from_table('address').limit(100).offset(200).get_query_string()

Out[54]:

'SELECT * FROM address LIMIT 100 OFFSET 200;'

join_table()

In [55]:

builder = SelectBuilder()
builder.from_table('address').join_table('contact')
builder.where('address.state_code', '=', " contact.state_code ")
builder.get_query_string()

Out[55]:

'SELECT * FROM address INNER JOIN contact WHERE address.state_code = contact.state_code;'

Variety Examples

In [67]:

def select_like_many():
    word_list = ['json', 'query']
    not_list = ['javascript', 'jquery']

    builder = SelectBuilder()
    builder.from_table('pypi_list').column(['package_name','package_description'])

    for column in word_list:
        value_string = " '%{0}%' ".format(column)
        builder.where('package_description', 'LIKE ', value_string, 'AND')

    for column in not_list:
        value_string = " '%{0}%'".format(column)
        builder.where('package_description', 'NOT LIKE ', value_string, 'AND')

    return(builder.get_query_string(True)) # format sql

In [70]:

query_result = select_like_many()
print(query_result)

SELECT package_description,
       package_name
FROM pypi_list
WHERE package_description LIKE '%json%'
  AND package_description LIKE '%query%'
  AND package_description NOT LIKE '%javascript%'
  AND package_description NOT LIKE '%jquery%';

InsertBuilder

import

In [58]:

from sqlstring.sql_builder import InsertBuilder

Basic Examples

into_table()

In [59]:

builder = InsertBuilder()
builder.into_table('address').column(['city', 'state_code']).get_query_string()

Out[59]:

'INSERT INTO address (city, state_code) VALUES (:city, :state_code);'

into_table() with 'OR REPLACE' key word

In [60]:

builder = InsertBuilder()
builder.into_table('address').column(['city', 'state_code'])
builder.get_query_string('OR REPLACE')

Out[60]:

'INSERT OR REPLACE INTO address (city, state_code) VALUES (:city, :state_code);'

UpdateBuilder

import

In [61]:

from sqlstring.sql_builder import UpdateBuilder

Basic Examples

update_table()

In [62]:

builder = UpdateBuilder()
builder.update_table('address').set('state_name', " 'California' ")
builder.get_query_string()

Out[62]:

"UPDATE address SET state_name = 'California' ;"

update_table() with where()

In [63]:

builder = UpdateBuilder()
builder.update_table('address').set('state_name', " 'California' ")
builder.where('state_code', '=', " 'CA' ").get_query_string()

Out[63]:

"UPDATE address SET state_name = 'California' WHERE state_code = 'CA';"

DeleteBuilder

import

In [64]:

from sqlstring.sql_builder import DeleteBuilder

Basic Examples

from_table()

In [65]:

builder = DeleteBuilder()
builder.from_table('address').get_query_string()

Out[65]:

'DELETE FROM address;'

from_table with where()

In [66]:

builder = DeleteBuilder()
builder.from_table('address').where('state_code', '=', " 'CA' ")
builder.where('city', '=', " 'Oakland' ", 'AND')
builder.get_query_string()

Out[66]:

"DELETE FROM address WHERE state_code = 'CA' AND city = 'Oakland';"