the overall approach to SQLite

scacchicgardenSoftware and s/w Development

Dec 13, 2013 (3 years and 6 months ago)

56 views

2012
-
11
-
14

1

Python
SQLite Module

the overall approach to SQLite


Working with SQLite databases is a two
-
part
process

-
Python commands to access the
db

file

-
SQLite commands to work with the
db

contents


To use an SQLite database:

-
connect to it ("open" it)

-
set a cursor

-
execute SQLite commands

-
fetch records produced by SQLite commands

-
close things up when finished



2012
-
11
-
14

2

example

#!/
usr
/bin/
env

python

import sqlite3, sys


con = None

try
:


con =
sqlite3.connect
('
test.db
')


cur =
con.cursor
()


cur.execute
('SELECT SQLITE_VERSION()' )


data =
cur.fetchone
()



print
"SQLite version: %s" % data


cur.close
()


except
sqlite3.Error, e:


print "
Error
%s:" %
e.args
[0]


sys.exit
(1)



finally:


if
con:


con.close
()

sqlite3


connect()

-
returns a Connection() object


Connection.cursor
()

-
walks through selected records


Connection.cursor.execute
()

-
Performs a query to generate a result set of records


Connection.close
(),

Connection.cursor.close
()

-
Finalize changes to a database

2012
-
11
-
14

3

Accessing Records


Connection.cursor.fetchall()

-
reads all records in set, returns a list


Connection.cursor.fetchone()

-
reads one record from set and returns it


Connection.cursor.fetchmany()

-
reads a group of records, into a buffer

Accessing Records


Some SQLite statements can change a
database

-
CREATE_TABLE

-
INSERT_INTO

-
DELETE_FROM

-
DROP_TABLE


Connection.cursor.commit
()

-
save any changes made


see

www.sqlite.org/lang.html

2012
-
11
-
14

4

some SQLite3 commands



SELECT SQLITE_VERSION()

-
SELECT SQLITE_VERSION()


CREATE TABLE
table_name

(
field

type,
field

type, …)

-
CREATE TABLE Cars(Id INT, Name TEXT, Price INT
)


INSERT INTO
table_name

VALUES (
field
,
field
, …)

-
INSERT INTO Cars VALUES(3,'Skoda',9000
)


SELECT * FROM
table_name

-
SELECT * FROM
Cars


DROP
TABLE IF EXISTS
table_name

-
DROP
TABLE IF EXISTS
Cars


More examples…

-
"UPDATE
Cars
SET
Price=? WHERE Id=?", (
uPrice
,
uId
)

-
"SELECT Name, Price FROM Cars WHERE Id=:Id",

{"Id":
uId
}



SELECT * FROM
sqlite_master

WHERE type="table"


PRAGMA
table_info
(
table_name
)

Create a Database

#!/
usr
/bin/
env

python

import sqlite3, sys


mydatabase
="
cars.db
"

connection=sqlite3.connect(
mydatabase
)

cursor=
connection.cursor
()


cursor.execute
('CREATE TABLE cars (Id
INTEGER PRIMARY KEY, year INT, brand
TEXT, model TEXT, color TEXT)')


cmd

= 'INSERT INTO cars VALUES(null, ?,
?, ?, ?)'

cursor.execute
(
cmd
, (1972, 'Dodge',
'van', 'rust brown
'))


cursor.execute
(
cmd
, (1982,
'Subaru', '4WDGL', 'beige'))

cursor.execute
(
cmd
, (1997, 'Jeep',
'Cherokee Sport', 'gunmetal blue'))

cursor.execute
(
cmd
, (2013,
'Subaru', 'Outback Limited SAP',
'brilliant pearl brown'))

connection.commit
()


cursor.close
()

connection.close
()

Create a database

consisting of one table,

containing four records

2012
-
11
-
14

5

Inspect a Database

#!/
usr
/bin/
env

python

import sqlite3, sys

print today


mydatabase
="
cars.db
"

connection=sqlite3.connect(
mydatabase
)

cursor=
connection.cursor
()


#
check tables


cursor.execute
('SELECT
name
FROM
sqlite_master

WHERE type="table";')

entries =
cursor.fetchall
()

for e in entries
:


print
e


Find out what

tables are in

this database

Access a Database

#!/
usr
/bin/
env

python

import sqlite3, sys

print today


mydatabase
="
cars.db
"

connection=sqlite3.connect(
mydatabase
)

cursor=
connection.cursor
()


# grab everything


cursor.execute
('SELECT * FROM cars')

entries =
cursor.fetchall
()

for e in entries: # e is a tuple


print '%3d %4d %8s %20s (%s)' % e



# Pick and choose


choice = 'Subaru'

print choice, ':'

cursor.execute
(


'SELECT model, color FROM cars
WHERE brand=:BR',


{'
BR':choice
})

entries =
cursor.fetchall
()

for e in entries:


print e


cursor.close
()

connection.close
()

Extract records

from a database table

and display them

2012
-
11
-
14

6

Look at Firefox cookies

#!/
usr
/bin/
env

python

import sqlite3, sys


filename = '
cookies.sqlite
'


conn = sqlite3.connect( filename
)

cursor =
conn.cursor
()

mySQLquery

= 'SELECT id FROM
moz_cookies
'

id =
cursor.execute
(
mySQLquery

)

idlist

=
id.fetchall
()

print
len
(
idlist
), "cookies"

print
idlist
[0]



for
i

in
idlist
[5:] :


query2 = 'DELETE FROM
moz_cookies

WHERE id=%d' %
i


cursor.execute
(query2)

conn.commit
()


id =
cursor.execute
(
mySQLquery

)

idlist

=
id.fetchall
()

print
len
(
idlist
), "cookies"

print
idlist
[0
]

An alternative look at the version

#!/
usr
/bin/
env

python

import
struct


filename = '
cookies.sqlite
'

handle = open(filename, ‘r’)

handle.seek
(96) # or 0x60


s =
handle.read
(4)

handle.close
()


version =
struct.unpack
( ‘>I’, s )

vs

= ‘%d’ % version

verstring

=
vs
[:1] + ‘.’ +
vs
[1:4] + ‘.’ +
vs
[4:7]


print
verstring

# may not be the same as SQLITE_VERSION() ?