usage of SQLite database from Python

In this article we talk about how to use SQLite with Python, SQLite is a SQL database engine that has these features: self-contained, serverless, zero-configuration and transactional. SQLite library included in from version 2.5, follow part is the start with SQLite/ Python, how to access SQLite database? Following Python code how to connect and selecting data/creating schema:

Code:



import sqlite3 as sdb

myConnection = sdb.connect('/tmp/test.db')
cursorObj = myConnection.cursor()
cursorObj.execute('SELECT SQLITE_VERSION()')
## printing the version value
Vdata = cursorObj.fetchone()
print "SQLite version: %s" % Vdata
## create a user table
cursorObj.execute('CREATE TABLE IF NOT EXISTS user( firstname VARCHAR(43), age INTEGER)')
##  add data to table user
cursorObj.execute('insert into user values (?,?)', ('sam',43) )
## get tables' info from sqlite_master
 

You also insert values from array:

Code:

my_data = (
    ('Jim','Sead'),
    ('Tom','Fadi'),
    ('Kely','Noras'),
    ('Eid','Esa')
)

cursorObj.executemany('INSERT INTO user(firstname,lastname) VALUES(?,?)',my_data)

myConnection .commit()
 

You can also get fields by it name like this

Code:


cursorObj.execute("SELECT * FROM user")

FetchedData = cursorObj.fetchall()

for record in FetchedData:
    print "%-2s %-10s " % (record ["firstname"], record ["lastname"])