« De-dupe but leave one of each with one SQL query | Main | Prototype-based Carousel »
October 09, 2009
PostgreSQL version of do_sql.py
import sys
import postgresql
from read_config import read_config
import time
import string
import re
def parse_insert_sql(sql):
response = {}
da_list = re.split("[\s\(\)]+", sql.lower().strip())
print(da_list)
ma_table = da_list[2]
if da_list[0] == 'insert':
response['is_insert'] = 1
if "returning %s_id" % (ma_table) in da_list:
response['sql'] = sql
else:
response['sql'] = sql + " returning %s_id as insert_id" % (ma_table)
else:
response['is_insert'] = 0
response['sql'] = sql
return response
def do_sql_query(db_config_file, sql, debug):
response = {}
results = ()
"""
Takes config file name, your SQL and a debug parameter.
Returns a dictionary with a boolean, an error and a warning string and
a list of results dictionaries.
"""
connection_map = read_config('%s.ini' % db_config_file, debug)
(host, user, passwd, db) = (connection_map['connection_parameters']['host'],
connection_map['connection_parameters']['user'],
connection_map['connection_parameters']['password'],
connection_map['connection_parameters']['db'])
connection = postgresql.open("pq://%s:%s@%s/%s" % (user, passwd, host, db))
insert_response = parse_insert_sql(sql)
if insert_response['is_insert'] == 1:
sql = insert_response['sql']
try:
results = connection.prepare(sql)
response['results'] = results
response['status'] = 1
if insert_response['is_insert'] == 1:
response['insert_id'] = results.first()
return(response)
except:
if debug == 1:
print ("\nPostgreSQL error: %s\n" % (sql))
response['status'] = 0
response['error'] = "\nPostgreSQL error: %s\n" % (sql)
response['results'] = results
return(response)
if __name__ == "__main__":
response = do_sql_query('waf_common', "insert into stuff(stuff_id, nonsense) values(nextval('stuff_seq'::regclass), 'Glug')", 0)
print (response['insert_id'])
response = do_sql_query('waf_common', "select * from staff limit 10", 0)
print (response['results'].first())
for record in response['results']:
for column in record.column_names:
print("%s : %s" % (column, record[column]))
print("\n ----------- \n")
Tags: Python
Posted by pj at October 9, 2009 05:00 PM