Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

python

oop

sqlite

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

like image 277
Steinthor.palsson Avatar asked Jan 06 '11 00:01

Steinthor.palsson


People also ask

Is cursor a function in sqlite3 module?

The sqlite3. Cursor class is an instance using which you can invoke methods that execute SQLite statements, fetch data from the result sets of the queries. You can create Cursor object using the cursor() method of the Connection object/class.

Does SQLite allow multiple connections?

The current version of SQLite handles multiple connections through its thread-mode options: single-thread, multi-thread, and serialized. Single-thread is the original SQLite processing mode, handling one transaction at a time, either a read or a write from one and only one connection.

What is Conn cursor ()?

Cursors are created by the connection. cursor() method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.

Which of the following term is not a function in sqlite3 module?

callback is not a function in sqlite.


1 Answers

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.

like image 107
monkut Avatar answered Oct 16 '22 21:10

monkut