SQL inside code or procedure?

Question

This is a very straight forward question regarding how to insert or select data from/to a database ? Since i'm trying to keep my code as clean as possible, this is how i'm actually performing queries and inserts/updates:

import sys
import MySQLdb
from ConfigParser import SafeConfigParser
#------------------------------------------------------------
# Select and insert
# this func should be called like:
# db_call('c:\dbconf.cfg','select * from something') 
# or insert / update statement.
#------------------------------------------------------------
def db_call(cfgFile, sql):
 parser = SafeConfigParser()
 parser.read(cfgFile)
 dbType = parser.get('database', 'db_type')
 db_host = parser.get('database', 'db_host')
 db_name = parser.get('database', 'db_name')
 db_user = parser.get('database', 'db_login')
 db_pass = parser.get('database', 'db_pass')
 con = MySQLdb.connect(host=db_host, db=db_name,
 user=db_user, passwd=db_pass
 )
 cur = con.cursor()
 try:
 try:
 cur.execute(sql)
 if re.match(r'INSERT|insert|UPDATE|update|DELETE|delete', sql):
 con.commit()
 else:
 data = cur.fetchall()
 resultList = []
 for data_out in data:
 resultList.append(data_out)
 return resultList
 except MySQLdb.Error, e:
 con.rollback()
 print "Error "
 print e.args
 sys.exit(1)
 else:
 con.commit()
 finally:
 con.close()

But, using this method i have to keep all the queries inside my main class, where that can be a problem if any change happens into the table structure,

But, going for sp call, i can have the code more clean, passing only the sp name and fields. But sometimes this could lead me to have one python function for more specific cases, ( as an example, sp that receives 2,3 or 4 inputs must have diferent python functions for each )

import sys
import MySQLdb
from ConfigParser import SafeConfigParser
#------------------------------------------------------------
# Select only!!!!!!
# this func should be called like:
# db_call('fn_your_function','field_a','field_b') 
# or insert / update statement.
#------------------------------------------------------------
def db_call(self, cfgFile, query):
 parser = SafeConfigParser()
 parser.read(cfgFile)
 dbType = parser.get('database', 'db_type')
 db_host = parser.get('database', 'db_host')
 db_name = parser.get('database', 'db_name')
 db_user = parser.get('database', 'db_login')
 db_pass = parser.get('database', 'db_pass')
 con = MySQLdb.connect(host=db_host, db=db_name,
 user=db_user, passwd=db_pass
 )
 cur = con.cursor()
 try:
 cur.callproc(query[0], (query[1],query[2]))
 data = cur.fetchall()
 resultList = []
 for data_out in data:
 resultList.append(data_out)
 return resultList
 con.close()
 except MySQLdb.Error, e:
 con.rollback()
 print "Error "
 print e.args
 sys.exit(1)

Im not sure if here is the right place to ask this, but before voting to close it (if is the case ) please reply with the information where i could ask this kind of question :)

Thanks in advance.

Answer 1

If your goal is to abstract away the schema of your DB from your objects' implementations, you should probably be looking at ORMs/persistence frameworks. There are a number of them in Python. As examples, SQLAlchemy is popular and Django, a popular web framework, has one built in.

• thclpr: i was looking into SQLAlchemy , i did some performance tests with inserts ( 50k fields ) and i found it more slower than mysqldb.
• Silas Ray: Well yeah, it's going to be slower. It is another layer of processing between you and the DB. The trade off is that your DB is abstracted away. Just like almost every problem in software engineering, it's a trade off. Here, you trade some amount of speed and memory usage for code maintainability and extensability. Though one could argue that if you are worrying that much about speed, you should probably be working in a lower level language, at least in part. You could always look at using a C persistence framework and wrapping it in a Python extension.
• thclpr: The main problem is im not a real developer, im just starting on python and i even dont remember c code. The way the tool works is good. The main problem for me, is if i choose using procedures, i need to have one py function for specific cases ( as i wrote on the example ) but if going to sqlalchemy costs me some performance i would like to stay on mysqlDB.
• thclpr: Im marking your question as correct because i follow and aggree with it. But im not doing sqlalchemy because of lack of performance.