CODEWITHSUNDEEP
pythonsqlite
Biggen
Biggen

Reputation: 295

Using functions to open/close sqlite db connections?

Being new to both Python and sqlite, I've been playing around with them both recently trying to figure things out. In particular with sqlite I've learned how to open/close/commit data to a db. But now I'm trying to clean things up a bit so that I can open/close the db via function calls. For instance, I'd like to do something like:

def open_db():
    conn = sqlite3.connect("path")
    c = conn.cursor()

def close_db():    
    c.close()
    conn.close()

def create_db():
    open_db()
    c.execute("CREATE STUFF")
    close_db()

Then when I run the program, before I query or write to the table, I could do something like:

open_db()
c.execute('SELECT * DO STUFF')
OR
c.execute('DELETE * DO OTHER STUFF')
conn.commit
close_db()

I've read about context managers but I'm not sure I understand entirely whats going on with them. What would be the easiest solution to cleaning up the way I open/close my DB connections so I'm not always having to type in the cursor command.

Upvotes: 2

Views: 3422

Answers (2)

e4c5
e4c5

Reputation: 53774

This is because the connection you define is local to the open db function. Change it as follows

def open_db():
    conn = sqlite3.connect("path")
    return conn.cursor()

and then

c = open_db()
c.execute('SELECT * DO STUFF')

It should be noted that writing function like this purely as a learning exercise might be ok, but generally it's not very useful to write a thin wrapper around a database connectivity api.

Upvotes: 1

Bill Bell
Bill Bell

Reputation: 21663

I don't know that there is an easy way. As already suggested, if you make the name of a database cursor or connection local to a function then these will be lost upon exit from that function. The answer might be to write code using the contextlib module (which is included with the Python distribution, and documented in the help file); I wouldn't call that easy. The documentation for sqlite3 does mention that connection objects can be used as context managers; I suspect you've already noticed that. I also see that there's some sort of context manager for MySQL but I haven't used it.

Upvotes: 1

Related Questions