Using functions to open/close sqlite db connections?

Question

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.

Jack Deeth · Accepted Answer · 2024-12-03 17:40:54Z

1

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.

edited Dec 3, 2024 at 17:40

Jack Deeth

3,4994 gold badges30 silver badges46 bronze badges

answered May 7, 2017 at 13:16

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Biggen Over a year ago

Thanks for that. What is the appropriate way to handle opening and closing a db then if not this??

e4c5 Over a year ago

what I am saying is that your functions do not do anything really usefull. Just leave them out. and BTW, long term you will not be using the python database apis at all. You will eventually want to adapt an ORM like SQLAlchemy.

Biggen Over a year ago

ok then I understand. So unless I want to use an ORM its best to simply make the cursor and conn global and just type them in every time I need to open/close the db instead of making these very simplistic functions to handle this.

e4c5 Over a year ago

if you use an ORM, you wouldn't be using sqlite3.connect at all :-)

e4c5 Over a year ago

yes, for simple code, just open the connection at the top of the script and use it without creating a function. if you have several modules and you find that you want the same connection to be used in all of them (DRY principal), that's the time to start seriously thinking about an ORM

Bill Bell · Accepted Answer · 2017-05-07 13:30:57Z

1

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.

answered May 7, 2017 at 13:30

Bill Bell

21.7k6 gold badges48 silver badges62 bronze badges

Collectives™ on Stack Overflow

Using functions to open/close sqlite db connections?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related