@dgelessus , Thanks so much for your feedback. I was really making life hard for myself :) but I did read a lot about nametuples being good for use with SQLite. That's why I went down that path. But was not using my own brain. Maybe some might still say they are better. But as you point out, it seems like you have to jump through a lot of hoops to coerce things the way you want them to work. I reworked the code Based on your feedback. I think I did it ok, but I could be still doing somethings wrong. But I feel better about the dict version vrs the namedtuple version. Mind you, I do like the dot notation possible with namedtuple's.
Again, what I posted is only a portion of what I am writing. I have a class to wrap this up in. This version I added the dict_factory, I also had one for the namedtuple version also.
Hmmm, about with and conn, it's a nightmare :)
I implemented enter and exit methods in my class, and that's all great if your object is called using the with clause. But if not, what to do. I looked and looked for strategies to deal with this. I could not find anything.
It seems to me it would be excellent if a class could be told, hey you should always call enter and exit wether or not with is used. I did also look at the ContextManager and using decorators, that also does not seem to provide an elegant solution. I also realise the connection object itself implements enter and exit. I also realise it can handle nested calls. I tried using with conn in every method in my class that used a connection. Whilst things didn't break, I felt out of control. In the class I tried to break down the methods into reusable code, as you do. So just getting a count of records from a table etc... But with this modular approach you end up with many levels of contexts. While I didn't get the gravity of really what was going on, I had the feeling I was creating a big problem for myself.
Given that I am just trying to write a simple app storage utility, I decided to keep the connection open. I have a method to close the connection also. But every method that requires a connection, requests one from a method. If there is a open connection we return that, otherwise reconnect to the Database save the connection in a attr and return it. So, the user if the class does not have to do much. Eg, if you want to read or write some values on startup, you can do so and just call close. Next time you call a method on the class, if the connection is closed, it will just reopen it. Conversely if you are using it throughout the app, no real need to close it as its a single connection.
Also doing other various things for bulk inserts/updates etc to avoid commits after each execute. I know can use executemany etc... Sometimes not convenient when going though a class. I have done nothing with transactions yet. Later :)
Sorry, I know I went on about this. But this context manager can do your head in. Look maybe I have it all wrong. But I did spend a lot of time looking and reading. Maybe the wrong things :)
But again thanks for your suggestions and help. I really feel, it helped me a lot.
# coding: utf-8
# coding: utf-8
from collections import OrderedDict
import sqlite3
from random import randint
from faker import Faker
fake = Faker()
db_def ={
'db_name': 'test.db',
# some other fields to come later...
'flds' : OrderedDict((('id','INTEGER PRIMARY KEY'),
('resid','INTEGER UNIQUE'),
('key','TEXT') ,
('ord','INTEGER'),
('value','INTEGER'),
('value1','TEXT'),
('data','TEXT'),
('pickled','INTEGER')))
}
# diervived from our db_def[field_names]
REC = OrderedDict((attr, None) for attr in db_def['flds'].keys())
_table_sql_new = '''CREATE TABLE IF NOT EXISTS '{0}' ({1})'''.format('{0}', ', '.join( '{0} {1}'.format(k, v) for k,v in db_def['flds'].items()))
insert_pattern = '({0})'.format(", ".join("?" * len(db_def['flds'])) )
_insert_sql = ''' INSERT INTO {0} VALUES ''' + insert_pattern
def new_record(**kwargs):
# not sure if i can do this better or not.
# create a empty record with all fields set to None
#rec= OrderedDict((attr, None) for attr in db_def['flds'].keys())
rec = OrderedDict(REC)
for k,v in kwargs.iteritems():
if rec.has_key(k):
rec[k] = v
return rec
def dict_factory(cursor, row):
#d = OrderedDict((attr, None) for attr in db_def['flds'].keys())
rec = OrderedDict(REC)
for idx, col in enumerate(cursor.description):
rec[col[0]] = row[idx]
return rec
if __name__ == '__main__':
# not that is really matters in this case, but because the id's
# are different, i have create a new copy of db_def because i
# called dict(db_def), if i just do mydb_def = db_def ids are the
# same. makes sense. Simple stuff but easy for us newbies to slip
# up on these small things.
mydb_def = dict(db_def)
print id(mydb_def), id(db_def)
db_name = mydb_def['db_name']
db_table = 'table_c'
recs_to_add = 2
conn = sqlite3.connect(db_name)
with conn:
conn.execute(_table_sql_new.format(db_table))
# using randint() for testing...resid is unquie
for i in range(1, recs_to_add):
rnd_resid = randint(1, 500000)
r = new_record(resid = rnd_resid, key = fake.city(), data = fake.first_name(), bad_keyword = 'bad info')
print r.values()[0]
conn.execute(_insert_sql.format(db_table), r.values())
conn.commit()
conn.row_factory = dict_factory
cur = conn.execute('SELECT * FROM {0}'.format(db_table))
for d in cur:
print d
