Usage Examples

In [47]:

    

from sqlstring.sql_builder import SelectBuilder


    

In [48]:

    

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


    

    
Out[48]:





'SELECT * FROM address;'

In [49]:

    

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


    

    
Out[49]:





'SELECT DISTINCT * FROM address;'

In [50]:

    

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


    

    
Out[50]:





'SELECT city, state_code FROM address;'

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';"

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;'

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;'

In [54]:

    

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


    

    
Out[54]:





'SELECT * FROM address LIMIT 100 OFFSET 200;'

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;'

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%';

In [58]:

    

from sqlstring.sql_builder import InsertBuilder


    

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);'

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);'

In [61]:

    

from sqlstring.sql_builder import UpdateBuilder


    

In [62]:

    

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


    

    
Out[62]:





"UPDATE address SET state_name = 'California' ;"

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';"

In [64]:

    

from sqlstring.sql_builder import DeleteBuilder


    

In [65]:

    

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


    

    
Out[65]:





'DELETE FROM address;'

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';"