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

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

---

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.

---

It's failing because your function closes the connection before returning. The way to fix this is to turn the function into a generator that passes on the results. Something like the following untested code should work:

def connection (db, arg):
    conn = sqlite3.connect(db)
    conn.execute('pragma foreign_keys = on')
    cur = conn.cursor()
    cur.execute(arg)
    for row in cur:
        yield row
    conn.commit()
    conn.close()

You have to be extra careful to consume all the rows when calling this function, because if you don't then the connection won't be closed. You might be able to avoid this problem by looking at implementing the functions necessary for the with syntax.