How To Connect To Multiple sqlite3 Database with Python

Question

this is somewhat related to my earlier query..

The problem was with runtime, so i was suggested to use sqlite3 database, and it reduced the time to millisecond, and I am very happy, now the only problem i have is, connecting to different database files in the same folder. All the database files have the same tables.

The code I am using, reads only the first one, and doesnt seem to check the other databases.

The output is when the teacher, enters students ID, it is supposed to return the related records if found in the database table.

my Code is something like this, But I am sure I am doing something wrong, pardon me if its a silly one, as using sqlite3 for the first time.

#other codes above not related to this part
databases = []
directory = "./Databases"
for filename in os.listdir(directory):
    flname = os.path.join(directory, filename)
    databases.append(flname)

for database in databases:
    conn = sqlite3.connect(database)
    conn.text_factory = str
    cur = conn.cursor()
    sqlqry = "SELECT * FROM tbl_1 WHERE std_ID='%s';" % (sudentID)
    try:
        c = cur.execute(sqlqry)
        data = c.fetchall()
        for i in data:
            print "[INFO] RECORD FOUND"
            print "[INFO] STUDENT ID: "+i[1]
            print "[INFO] STUDENT NAME: "+i[2]
            #and some other info
        conn.close()
    except sqlite3.Error as e:
        print "[INFO] "+e

Thanks For Any guides

I don't see how this code can possible work, it should generate a NameError on sudentID — Daenyth
– Daenyth, Commented Aug 7, 2012 at 17:09
Some aside comments: I would write 1) databases = [os.path.join(directory, filename) for filename in os.listdir(directory)]; 2) with sqlite3.connect(database) as conn: ... — khachik
– khachik, Commented Aug 7, 2012 at 17:11
How do you know it's not checking other files? Have you tried to add a diagnostic print? Like print database for instance. What's the output? — Lev Levitsky
– Lev Levitsky, Commented Aug 7, 2012 at 17:13
What is your table structure, and how did you get the data from your 10 text files into your databases? (Also, why 10? Are they broken up by date or some other thing? You could maybe put them all in one table with an additional date field.) @LevLevitsky is suggesting to print the name of the database as you open it, to verify that you're getting what you think you're getting. Also do print len(data) to show how many records you are getting back. In general, people would be able to help you better if you post a single example that demonstrates the issue, and not omit possibly important code. — Todd
– Todd, Commented Aug 7, 2012 at 17:29

Edward L · Accepted Answer · 2012-08-07 18:05:15Z

2

@Whiskey, sometimes it helps to try to break the problem down into a minimal example and see if that works or where it breaks. Since you are able to see the database names being printed as they are opened, my guess would be a problem with the query or possibly the data in the db even tho the records seem to be there. When you say it doesn't find the record you're looking for does it just print out nothing or does it print out the "[INFO]" line in your exception handler?

I put together the following minimal example, and it seems to be working as far as my understanding of your problem goes. My only other piece of advice to add to everyone else's would be to parametrize your query rather than using the raw input directly to make your app a little more secure. Hope it helps:

import os, sqlite3


"""
Create the test databases:

sqlite3 Databases/test_db1.db

sqlite> CREATE TABLE foo ( id INTEGER NOT NULL, name VARCHAR(100), PRIMARY KEY (id) );
sqlite> 


sqlite3 Databases/test_db2.db
sqlite> CREATE TABLE foo ( id INTEGER NOT NULL, name VARCHAR(100), PRIMARY KEY (id) );
sqlite> INSERT INTO foo VALUES (2, 'world');

"""


databases = []
student_id = 2

directory = "./Databases"
for filename in os.listdir(directory):
    flname = os.path.join(directory, filename)
    databases.append(flname)

for database in databases:

    try:

        with sqlite3.connect(database) as conn:

            conn.text_factory = str
            cur = conn.cursor()
            sqlqry = "SELECT * FROM foo WHERE id=:1;"
            c = cur.execute(sqlqry, [student_id])

            for row in c.fetchall():
                print "-- found: %s=%s" % (row[0], row[1])

    except sqlite3.Error, err:
        print "[INFO] %s" % err

answered Aug 7, 2012 at 18:05

Edward L

562 bronze badges

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

2 Comments

Whiskey Over a year ago

Awesome , million thanks, now it reads, it database files, cheers, you kinda saved my job..appreciated.. but i did not understnad still why my structure dint work, probably i wil look into it, after i get over this headache.. once again thanks..

Todd Over a year ago

Your actual code didn't work as we discovered in chat because you are ignoring several types of exceptions. If you log or print your exception messages, you will find the error.

Collectives™ on Stack Overflow

How To Connect To Multiple sqlite3 Database with Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related