Python. Data bases. Marcin Młotkowski. 8th May, 2013

(1)

Data bases

Marcin Młotkowski

(2)

DBM databases Object persistence SQL data bases and data engines

1 DBM databases

2 Object persistence

3 SQL data bases and data engines

(3)

Types of data storage

Bekreley DB (n)dbm Gnu dbm Trwałe słowniki Oracle DB/2 MySQL PostgeSQL Firebird Sqlite Klient-serwer SQL

(4)

DBM databases

Object persistence SQL data bases and data engines

DBM databases

Database manager

Data is stored in file being a hashtable Values are accessed by keys

Usually, value can be only of type string

Implementation: standard hash tables and b-trees No server, all in one file

Fast!!!

(5)

DBM databases

How to use DBM

db =<modul>.open(’storage.dbm’, ’c’) db[’jeden’] = ’one’ db[’dwa’] = ’two’ if ’trzy’in db: del db[’trzy’] db.close()

(6)

DBM databases

How to use DBM

import<modul> db =<modul>.open(’storage.dbm’, ’c’) db[’jeden’] = ’one’ db[’dwa’] = ’two’ if ’trzy’in db: del db[’trzy’] db.close()

(7)

Available modules

Python 2.* Python 3.* Description

dumbdb dbm.dumb slow, but pure Python implementation

bsddb (dbhash) — implementation of Berkeley DB

dbm dbm.ndbm API to U*X (n)dbm

(8)

DBM databases

How to process entire collection

forkey in db.keys():

print db[key]

Warning

All file must be kept in memory.

(9)

How to process entire collection

forkey in db.keys():

print db[key] Warning

(10)

DBM databases

Collection processing, dumbdbm

forkey in db:

print ’db[’, key, ’] =’, db[key] db.close()

(11)

Collection processing

dbhash printdb.first() whileTrue: try: el = db.next() print el except KeyError: break gdbm k = db.firstkey() whilek != None: print k k = db.nextkey(k)

(12)

DBM databases

Available modules

for–in firstkey(), nextkey() first(), next()

dumbdbm X — —

(n)dbm — — —

gdbm — X —

dbhash — — X

(13)

DBM databases

how to deal with this mess

Use generic packageanydbm

>>>importanydbm

(14)

DBM databases

how to deal with this mess

Use generic packageanydbm

Use packagewhichdbm:

>>>importanydbm

>>>whichdb.whichdb(’plik.db’)

(15)

Berkeley DB

Who use Berkeley DB?

OpenLDAP, Subversion, Spamassasin, KDevelop, ... Features:

transactions; replication; record locking;

(16)

DBM databases

Object persistence

SQL data bases and data engines

How to store object?

shelvepackage

Shelve file is a dictionary, i.e. list of pairs(string, object)

(17)

shelve package

Example importshelve db = shelve.open(’dane.db’) db[’a_list’] = [2,3,5,7,11] db.sync() deldb[’an_object’] db.close()

(18)

DBM databases Object persistence

Database engines

Oracle DB/2 MySQL PostgreSQL MSSQL ...

(19)

DB API

Python Database API Specification

A unified API (methods and fields) to different database engines. Current version: 2.0.

(20)

Opening connection to database

connect(’parameters’) # returns an object Connection

MySQL importMySQLdb db = MySQLdb.connect(host=’localhost’, db=’testing’, user=’user’, passwd=’123456’)

(21)

Opening connection to database

connect(’parameters’) # returns an object Connection

MySQL importMySQLdb db = MySQLdb.connect(host=’localhost’, db=’testing’, user=’user’, passwd=’123456’)

(22)

Closing connection

db.close()

(23)

Communication with database

send a query

wynik = db.cursor()

wynik.execute(’SELECT * FROM Students’)

row = wynik.fetchone() whilerow: print row row = wynik.fetchone() Optional wynik.close()

(24)

Communication with database

send a query

wynik = db.cursor()

wynik.execute(’SELECT * FROM Students’) Fetching a result row = wynik.fetchone() whilerow: print row row = wynik.fetchone() Optional wynik.close()

(25)

A result

Result attributes (an object of the class Cursor): description: describes columns

rowcount: number of returned or processed (e.g. INSERT or UPDATE)rows

(26)

DB API - additional information

Standard exceptions:

Warning, DatabaseError, NotSupportedError, ...

(27)

SQLite

’File’ database, no external server, no contact with admin; module: sqlite3

(28)

Sqlite usage

Table creation

importsqlite3

db = sqlite.connect(’/tmp/temp.db’) kursor = db.cursor()

kursor.execute(” ’create table Library

(Author text, Title text, publish_year int, price real)” ’) kursor.commit()

(29)

Sqlite usage

Insert row into table

kursor.execute(” ’insert into Library values (’Shakespeare’, ’Hamlet’, 2003, 25.5)” ’)

(30)

Sqlite usage

Fetching data (extension of DB API 2.0) kursor.execute(’SELECT * FROM Library’)

forrow in kursor:

print row

(31)

Sqlite

Curiosity