can I put my sqlite connection and cursor in a function?

Question

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, cubby):
    conn = sqlite3.connect(db)
    conn.execute('pragma foreign_keys = on')
    cur = conn.cursor()
    cur.execute(arg)
    for row in cur:
        cubby.append(row)
    conn.commit()
    conn.close()

cubby=[]
connection('testdb.db', "create table users ('user', 'email')", cubby)
connection('testdb.db', "insert into users ('user', 'email') values ('joey', 'foo@bar')", cubby)
for row in connection('testdb45.db', "select * from users", cubby):
    print row

How can I make this work?

EDIT: modified the code a little so the cur values so it's appended to an outside list, but still pretty bad

Answer 1

I think the problem is a little more difficult than it looks at first.

You're seeing the error because you've closed your connection to the database in your "connection" function.

Your probably better off creating a DatabaseManagement Class, to manage a single connection.

Something like:

import sqlite3

class DatabaseManager(object):
    def __init__(self, db):
        self.conn = sqlite3.connect(db)
        self.conn.execute('pragma foreign_keys = on')
        self.conn.commit()
        self.cur = self.conn.cursor()

    def query(self, arg):
        self.cur.execute(arg)
        self.conn.commit()
        return self.cur

    def __del__(self):
        self.conn.close()

Then you should be able to do something like:

dbmgr = DatabaseManager("testdb.db")
for row in dbmgr.query("select * from users"):
    print row

This will keep the connection open for the duration of the object's existence.

You still may find this is a deeper problem, but play around and see what works for you.