Can I add a join and cursor to a function?

Question

I thought that I would try to connect the sqlite db function to the function instead of copying / pasting ~ 6 lines needed to connect and execute the query around the place. I would like to make it universal, so I can use the same function to create / select / insert / etc ...

Below I have tried. The "INSERT" and "CREATE TABLE" queries work, but if I make a "SELECT" query, how can I work with the values ​​that it selects outside the function?

Usually I want to print the values ​​that it gets, as well as do other things with them.

When I do this as shown 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 think the connection should be open, so I can get the values ​​from the cursor, but I need to close it so that the file is not always locked.

Here is my test 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 do this job?

EDIT: the code has changed a bit, so the cur values ​​that it adds to the external list, but still pretty bad

Answer 1

I think the problem is a little more complicated than at first.

You see an error because you closed your database connection in your connection function.

You might be 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 can 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 lifetime of the object.

You can still find this to be a deeper problem, but play around and see what works for you.

Answer 2

This is not the case because your function closes the connection before returning. The way to fix this is to turn the function into a generator that conveys the results. Something like the following unverified 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 must be careful to consume all rows when calling this function, because if you do not, the connection will not be closed. You might be able to avoid this problem by looking at the implementation of the functions needed for the with syntax.