Managing a simple database with Python, SQLite and wxPython, 7 (includes a question)
Phase 2, wxPython April 20th, 2009And we’re back. After a couple of weeks of inactivity we will get back to our small soap-opera pf Python, wxPython and SQLite. Continuing in our database management code let’s check two other functions that changed since our first inception of the code. The first one is the insert_data function that looks like this now
def insert_data(self, values_list, insert_string):
'''inserts data in the database'''
if sys.platform == 'darwin':
(cursor, database) = link_db(self.db_path)
else:
(cursor, database) = link_db()
cursor.execute(insert_string % self.table_name, values_list)
database.commit()
database.close()
Basically no changes, apart from the obvious check for the current running operating system, which was explained in the last post. The other function to check is the update_data. This function is new and it wasn’t in the first version, but as it can be seen it has a problem being a “generic” function, because it contains information pertained to the table and database being used in the interface. This function basically received information that needs to be updated in the table’s fields and by using the SQL UPDATE ... SET edits and updates data in the changed fields. I have tried several different syntaxes to make the execute generic, mainly trying to pre-format the string without success. IF anyone reading this can help, I’d appreciate.
def update_data(self, values_list):
'''edits and updates fields'''
if sys.platform == 'darwin':
(cursor, database) = link_db(self.db_path)
else:
(cursor, database) = link_db()
cursor.execute("UPDATE bac SET projects = ?, comments = ?, temperature = ?, cell = ?, box = ?, tubes = ?, chromosome = ?, sdate = ?, clone = ?, source = ?, location1 = ?, startpos = ?, endpos = ?,
gene = ?, genelink = ?, dnaex = ?, validation = ?, pcr = ?, refs = ?, antibiotic = ? WHERE idbac = ?",
values_list['projects'], values_list['comments'], values_list['temperature'], values_list['cell'], values_list['box'], values_list['tubes'],
values_list['chromo'], values_list['date'], values_list['clone'], values_list['source'], values_list['location'], values_list['start'], values_list['end'],
values_list['gene'], values_list['genelink'], values_list['dna'], values_list['validation'], values_list['pcr'],
values_list['refs'], values_list['antibiotic'], values_list['idbac']))
database.commit()
database.close()
Anyway, I will explain the logic of the command (OK for a stop gap, but not as a definite solution). values_list is a dictionary that is passed to the function and contains the field names as keys and the new/changed information as values. The execute method simply parses the values from each key in the update string which is then sent to the database and table to be changed. Everything is committed and the database is closed.
As this is a “generic” function from a “generic” class the ideal scenario would be to the function to receive a pre-formatted string with all the information, as in the insert data function, and update the information in the database.
I would like to thank in advance anyone that can comment on this. Next time we will continue checking the generic class and finalize this part in order to start with the interface build process.
Previously in the series:
Part 1
Part 2
Part 3
Part 4
Part 5
Part 6
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_a.png?x-id=d0bb5d11-6f9d-8521-9a2f-6cd30868e375)
April 20th, 2009 at 4:18 pm
Try using named placeholders in the SQL statments
import sqlite3
con = sqlite3.connect(“:memory:”)
cur = con.cursor()
cur.execute(“create table people (name text, age real)”)
data = {“who”:”Yeltsin”, “age”:72}
# using (:who,:age) pulls values from the dictionary
cur.execute(“insert into people values (:who, :age)”, data)
cur.execute(“select * from people”)
print cur.fetchone()
#prints (u’Yeltsin’, 72.0)
data["age"] = 90.0
cur.execute(“UPDATE people SET age=:age WHERE name=:who”, data)
cur.execute(“select * from people”)
print cur.fetchone()
prints (u’Yeltsin’, 90.0)
con.close()
April 21st, 2009 at 2:21 am
The update statement looks really ugly here. Why not use SQLAlchemy or some kind of a mapper? For the start, you could generate the SQL on the fly yourself (i.e. “%s = ?” % name for all keys in the passed dict).
April 21st, 2009 at 3:27 am
I agree with Mike – use named placeholders. The update statement is then easy to construct using something like
bind = {‘foo’:1, ‘bar’:2}
insert = ‘,’.join(['%s=:%s'%(y,y) for y in bind])
cu.execute(‘update mytable set %s where id=:id’%insert, bind)
this assumes your column names are equivalent to the keys in your dictionary. Alternatively, google for ‘object relational mappers’ (sqlalchemy is probably the best known mapepr for python) – this is exactly the sort of thing they do well, but might be overkill for your application.
April 21st, 2009 at 12:23 pm
Adomas, indeed it really looks ugly, and that’s the main reason for my question.
As Richard mentioned, SQLAlchemy would be overkill here. I really like SQLAlchemy, but here we have one database with one table.
Thanks everyone for the suggestions, I’m trying the placeholders now.
April 21st, 2009 at 7:57 pm
The suggestion worked really well, thanks a lot. I’m posting the new code after some testing.