CODEWITHSUNDEEP
pythonsqlite
Whiskey
Whiskey

Reputation: 459

How To Connect To Multiple sqlite3 Database with Python

this is somewhat related to my earlier query..

Reading A Big File With Python

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

Upvotes: 4

Views: 9533

Answers (1)

Edward L
Edward L

Reputation: 56

@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

Upvotes: 2

Related Questions