Sqlite3 in python

[jv_05]

Excuse, I need to work with data bases. In a little research I made, I found that python has a library call sqlite3 but I don't find information of how I use it. So if anyone can explain it to me I'll be so thankful.

PD: I need this because I'm working on an application that manages tables.

[SoulMazer]

It just so happens I used this exact library to store information for my media player! I cleaned up the code a bit and just included some basic operations. (songDict is a dictionary which contains the name, artist, album, etc. of various songs -- I find it easiest to put information already in a dictionary into a database)

#!/usr/bin/python
# sqlite3 example

import sqlite3

"""
songDict:
the key is the song
songDict[0] = path
songDict[1] = artist
songDict[2] = album
"""

class Database:
    def __init__(self):
        try:
            self.conn = sqlite3.connect('songs.db')
        except sqlite3.OperationalError: # Can't locate database file
            exit(1)
        self.cursor = self.conn.cursor()
        
    def createDatabase(self):
        cmd = "CREATE TABLE allsongs(path VARCHAR(100), name VARCHAR(50), artist VARCHAR(50), album VARCHAR(50))"
        self.cursor.execute(cmd)
        self.conn.commit()
        
    def insertSongs(self, songDict):
        for song in songDict:
            cmd = """INSERT INTO allsongs(path, name, artist, album) VALUES("%s", "%s", "%s", "%s")""" % (song, songDict[song][0], songDict[song][1], songDict[song][2])
            print "Inserting", song+"..."
            self.cursor.execute(cmd)
        
        self.conn.commit()
        
    def getSongs(self):
        songDict = {}
        cmd = "SELECT * FROM allsongs"
        self.cursor.execute(cmd)
        results = self.cursor.fetchall()
        for song in results:
            songDict[song[0]] = (song[1], song[2], song[3])

        return songDict
        
    def closeHandle(self):
        'Closes the connection to the database'
        self.conn.commit() # Make sure all changes are saved
        self.conn.close()

If you want me to explain any of it, feel free to ask.

[jv_05]

It's really useful, I appreciate but I have some questions.
1. After creating a db, how do I call their objects?
2. The db must still running while my application works or only when I need to insert or call something in the db?
3. Can I create more then one db for my applicate? In the case of yes, how do I work with all of them (switch between db).
3. And if you can explain me more of how do I get something from the db.

PD: excuse me if I don't catch something quickly, thats because I'm new at python

[SoulMazer]

It's really useful, I appreciate but I have some questions.
1. After creating a db, how do I call their objects?
2. The db must still running while my application works or only when I need to insert or call something in the db?
3. Can I create more then one db for my applicate? In the case of yes, how do I work with all of them (switch between db).
3. And if you can explain me more of how do I get something from the db.

PD: excuse me if I don't catch something quickly, thats because I'm new at python

1. I'm not sure if I understand what you're asking. Are you asking how to manipulate a database after it has been created? I think you might be mixing up "table" and "database." The database is the entire thing, and it contains tables. A database can be created with this library by simply putting in any file name to a line of code like my line 17 (NOTE: When I refer to line numbers, I am referring to my last post). It doesn't matter if it already exists or not: if it does, the program simply accesses it; if it doesn't, the program creates a new database. Once you have this database, you can create "tables" inside of it. These tables are what actually contain your data...I am manipulating them in lines 27-41 of my code.

2. You can close the handle to the database whenever you are not directly accessing it.

3. Sure, why not? Just make sure you keep your variables straight.

self.db1 = sqlite3.connect('Database1.db')
self.db2 = sqlite3.connect('Database8935.db')
self.db3 = sqlite3.connect('SomeDatabase.db')
self.cursor1 = self.db1.cursor()
self.cursor2 = self.db2.cursor()
self.cursor3 = self.db3.cursor()

self.cursor1.execute("blah...")
self.cursor3.execute("more blah...")

4. Maybe a simpler example would help?

import sqlite3

database = sqlite3.connect('MyDatabase.db') # Create a database file
cursor = database.cursor() # Create a cursor
cursor.execute("CREATE TABLE IF NOT EXISTS mytable(name VARCHAR(20), zipcode VARCHAR(5))") # Make a table

# Insert some people into the table
cursor.execute("INSERT INTO mytable(name, zipcode) VALUES('Fred Johnson', '90608')")
cursor.execute("INSERT INTO mytable(name, zipcode) VALUES('John Doe', '48106')")
cursor.execute("INSERT INTO mytable(name, zipcode) VALUES('Joe Smith', '10928')")

database.commit() # Save our changes
database.close() # Close the connection to the database





database = sqlite3.connect('MyDatabase.db') # Open the database file
cursor = database.cursor() # Create a cursor

cursor.execute("SELECT * FROM mytable") # Select everyone in the table
results = cursor.fetchall()
for entry in results:
    print entry

database.close()

Any more questions?

[jv_05]

I think that with this I can move forward with my work. Thanks a lot. If I have more questions I'll be asking.

[jv_05]

Only a few questions more actually. In the part of:

cursor.execute("SELECT * FROM mytable") # Select everyone in the table
results = cursor.fetchall()
for entry in results:
    print entry

1. What is what I call (a string line, array, etc)??
2. And how do I call an specific data??

[SoulMazer]

Only a few questions more actually. In the part of:

cursor.execute("SELECT * FROM mytable") # Select everyone in the table
results = cursor.fetchall()
for entry in results:
    print entry

1. What is what I call (a string line, array, etc)??
2. And how do I call an specific data??

1. What do you mean? Are you asking what type of objects the results are? The actual variable 'results' is an array which contains an array for each entry. All of the data in these arrays is strings.

2. You mean how can you only select certain results from the table? Let's say you need to look up John Doe's zip code - just change line 22 from my last post into this: cursor.execute("SELECT * FROM mytable where name = 'John Doe'")

[jv_05]

I think that with this I'm gonna try to do my own application with db. Thanks. Any question I'll post it here.

[vegaseat]

Here is another code example using Python module sqlite3 that you can use to experiment with ...

# test module sqlite3 write and read a database file
# (Python25 and higher have module sqlite3 built in)
# sqlite3.connect(database, timeout=5.0, isolation_level=None,
#   detect_types=0, factory=100)
# keywords:
# timeout=5.0 --> allows multiple access for 5 seconds
# isolation_level=None -->  autocommit mode
# detect_types=0 --> native types TEXT, INTEGER, FLOAT, BLOB and NULL
# factory=100 --> statement cache to avoid SQL parsing overhead

import sqlite3

# create/connect to a permanent file database
con = sqlite3.connect("my_db.db3")

# establish the cursor, needed to execute the connected db
cur = con.cursor()

# create/execute a table:
# (optionally used capital letters to show commands)
cur.execute('CREATE TABLE IF NOT EXISTS clients \
    (id INT PRIMARY KEY, \
    firstname CHAR(60), \
    lastname CHAR(60))')

# insert several lines at once using a
# list of (id, firstname, lastname) tuples
# use try/except or the existing db will complain about
# the non-unique id since it is already in the db
try:
    clients = [
    (107, "Ella", "Fitzgerald"),
    (108, "Louis", "Armstrong"),
    (109, "Miles", "Davis")
    ]
    cur.executemany("INSERT INTO clients (id, firstname, lastname) \
        VALUES (?, ?, ?)", clients )
except:
    pass

# add another client
# use try/except or the existing db will complain about
# the non-unique id if it is already in the db
try:
    new_client = (110, "Benny", "Goodman")
    cur.execute("INSERT INTO clients (id, firstname, lastname) \
        VALUES (?, ?, ?)", new_client)
except:
    pass

# important if you make changes to the database
# commits current data to the db file (data is persistant now)
con.commit()

# now test it
# get data row by row
print("Show data row by row:")
# also orders/sorts data by lastname
cur.execute('SELECT id, firstname, lastname FROM clients \
    ORDER BY lastname')
for row in cur:
    print(row)

print('-'*40)

# select just one data item from each row ...
cur.execute('SELECT firstname FROM clients')
print(cur.fetchall())

print('-'*40)

# or ...
cur.execute('SELECT firstname FROM clients')
for row in cur:
    print(row[0])

print('-'*40)

# select a specific data row ...
cur.execute('SELECT * FROM clients WHERE lastname="Davis"')
print(cur.fetchall())

print('-'*40)

# show the table header
# use only the first item of the tuple info
col_name_list = [tup[0] for tup in cur.description]
print("Table header:")
print(col_name_list)

# finally ...
con.close()

"""my output with Python3 -->

Show data row by row:
(108, 'Louis', 'Armstrong')
(109, 'Miles', 'Davis')
(107, 'Ella', 'Fitzgerald')
(110, 'Benny', 'Goodman')
----------------------------------------
[('Ella',), ('Louis',), ('Miles',), ('Benny',)]
----------------------------------------
Ella
Louis
Miles
Benny
----------------------------------------
[(109, 'Miles', 'Davis')]
----------------------------------------
Table header:
['id', 'firstname', 'lastname']

"""