can I put my sqlite connection and cursor in a function?
- by steini
I was thinking I'd try to make my sqlite db connection a function instead of copy/pasting the ~6 lines needed to connect and execute a query all over the place.
I'd like to make it versatile so I can use the same function for create/select/insert/etc...
Below is what I have tried. The 'INSERT' and 'CREATE TABLE' queries are working, but if I do a 'SELECT' query, how can I work with the values it fetches outside of the function?
Usually I'd like to print the values it fetches and also do other things with them.
When I do it like below I get an error
Traceback (most recent call last):
File "C:\Users\steini\Desktop\py\database\test3.py", line 15, in <module>
for row in connection('testdb45.db', "select * from users"):
ProgrammingError: Cannot operate on a closed database.
So I guess the connection needs to be open so I can get the values from the cursor, but I need to close it so the file isn't always locked.
Here's my testing code:
import sqlite3
def connection (db, arg):
conn = sqlite3.connect(db)
conn.execute('pragma foreign_keys = on')
cur = conn.cursor()
cur.execute(arg)
conn.commit()
conn.close()
return cur
connection('testdb.db', "create table users ('user', 'email')")
connection('testdb.db', "insert into users ('user', 'email') values ('joey', 'foo@bar')")
for row in connection('testdb45.db', "select * from users"):
print row
How can I make this work?
