Jan Erik Moström, Department of Computing Science, Umeå University - jem@cs.umu.se
Databaser
Databaser
•
Finns några olika typer•
Relationsdatabaser är vanligast•
Vi kommer bara att prata om relationsdatabaserJan Erik Moström
Strukturerad information
•
Bygger på att man lagrar strukturerad information•
Begrepp:•
Databas•
Databashanterare•
Modell•
SchemaPrincip
Kalle Anka
Fågelvägen 12
123 45 Ankeborg 0191 123 238
Kajsa Anka Dungränd 3
123 82 Ankeborg 0191 234 832
Joakim von Anka Sedelvägen 99
123 32 Ankeborg 0191 992 991
Jan Erik Moström
Förnamn
Efternamn
Gatuadress Postnummer
Ort Telefon
Förnamn Efternamn Gatuadress Postnummer Ort Telefon
Kalle Anka Fågelvägen 12 123 45 Ankeborg 0191 123 238
Kajsa Anka Dungränd 123 82 Ankeborg 0191 234 832
Joakim von Anka Sedelvägen 99 123 32 Ankeborg 0191 992 991
Indexering Man indexerar inte allt
Jan Erik Moström
Organisera
Anta att vi har ett antal studenter som läser kurser på universitetet, då vill vi snabbt kunna se vem som har läst vad etc (tänk LADOK).
Hur ska man då göra för att lägga upp en databas för sådan info?
Förnamn Efternamn Personnr Gatuadr Ort Postnr telefon email kurs 1 ant kurs 1 klar
kurs 1 betyg lab 1 lab 2 lab 3 lab 4 lab 5 lab 6 lab 7 tenta 1 tenta 2
tenta 3 tenta 4 tenta 5 tenta 6 tenta 7 ansv lärare kurs 2 ant kurs 2 klar kurs 2 betyg ....
Bläääää
ID Förnamn Efternamn
Persontabell
ID Email
Emailtabell
etc
Kod Namn Datum
ID
Kurstabell
ID Telefon
Telefontabell
Personnr ID
Personnrtabell
Gatuadr Postnr Ort ID
Adresstabell
Jan Erik Moström
Persontabell
Emailtabell
Adresstabell
Telefontabell
Personnrtabell
Kurstabell
Persontabell Emailtabell
Kurstabell
EID PID EPC
PID KID PKC
Jan Erik Moström
SQL
•
Structured Query Language•
“Standard”•
Man skriver SQL och “bäddar in det” i andra språkExempel
import sqlite3 as dbman
mydb = dbman.connect('test.db') cursor = mydb.cursor()
cursor.execute('create table Person(first TEXT, last TEXT)') cursor.execute('insert into Person values ("Kalle","Anka")')
cursor.execute('insert into Person values ("Joakim","von Anka")') cursor.execute('insert into Person values ("Tjatte","Anka")')
cursor.execute('insert into Person values ("Fnatte","Anka")') cursor.execute('insert into Person values ("Knatte","Anka")')
cursor.execute('insert into Person values ("Alexander","Lucas")') mydb.commit()
Jan Erik Moström
cursor.execute('select * from Person') for item in cursor.fetchall():
print item
# (u'Kalle', u'Anka')
# (u'Joakim', u'von Anka')
# (u'Tjatte', u'Anka')
# (u'Fnatte', u'Anka')
# (u'Knatte', u'Anka')
# (u'Alexander', u'Lucas')
cursor.execute('select first from Person') for item in cursor.fetchall():
print item
# (u'Kalle',)
# (u'Joakim',)
# (u'Tjatte',)
# (u'Fnatte',)
# (u'Knatte',)
# (u'Alexander',)
cursor.execute('select * from Person') for item in cursor.fetchall():
print "%s har efternamnet %s" % (item[0],item[1])
# Kalle har efternamnet Anka
# Joakim har efternamnet von Anka
# Tjatte har efternamnet Anka
# Fnatte har efternamnet Anka
# Knatte har efternamnet Anka
# Alexander har efternamnet Lucas
cursor.execute('select * from Person where last = "Anka"') print "\n\nFamiljen Anka\n"
for item in cursor.fetchall():
print item[0]
# Familjen Anka
# # Kalle
# Tjatte
# Fnatte
Jan Erik Moström
cursor.execute('select * from Person order by last') for item in cursor.fetchall():
print "%s %s" % (item[0],item[1])
# Kalle Anka
# Tjatte Anka
# Fnatte Anka
# Knatte Anka
# Alexander Lucas
# Joakim von Anka mydb.close()
En person med flera emailadresser
Tjatte
4 Anka
2 Anka
Joakim Kalle
von Anka 1
Kajsa
3 Anka
Persontabell
kalle@ankeborg.se 2
joakim@ankeborg.se 1
jva@disney.com 1
joakim@pluring.se 1
Emailtabell
Jan Erik Moström
import sqlite3
db = sqlite3.connect('addresses.db') cursor = db.cursor()
# Create person table
cursor.execute('create table Person(first text, last text)')
# Create email table
cursor.execute('create table Email(adr text)')
# Insert some info into person table
cursor.execute('insert into Person(first,last) values ("Joakim","von Anka")')
#Insert some info into email table
cursor.execute('insert into Email(adr) values ("joakim@ankeborg.se")')
# Ehhhhhhh
# Let's start over by deleting everything cursor.execute('drop table Person')
cursor.execute('drop table Email')
# Create person table
cursor.execute('create table Person(pid integer primary key, first text, last text)')
# Create email table
cursor.execute('create table Email(adr text, belongsto integer)')
# Insert some info into person table
cursor.execute('insert into Person(first,last) values ("Joakim","von Anka")') jva = cursor.lastrowid
#Insert some info into email table
cursor.execute('insert into Email(adr,belongsto) values ("joakim@ankeborg.se",' + str(jva) + ')') cursor.execute('select * from Person inner join Email where Person.pid = Email.belongsto')
print cursor.fetchall()
[(1, u'Joakim', u'von Anka', u'joakim@ankeborg.se', 1)]
Jan Erik Moström
cursor.execute('''
select Person.first, Person.last, Email.adr from Person inner join Email
where Person.pid = Email.belongsto''' )
for item in cursor.fetchall():
print "%s %s\tEmail: %s" % (item[0],item[1],item[2])
Joakim von Anka Email: joakim@ankeborg.se
#Insert a second email address
cursor.execute('insert into Email(adr,belongsto) values ("joakim@pluring.se",' + str(jva) + ')')
cursor.execute('''
select Person.first, Person.last, Email.adr from Person inner join Email
where Person.pid = Email.belongsto''' )
for item in cursor.fetchall():
print "%s %s\tEmail: %s" % (item[0],item[1],item[2])
Joakim von Anka Email: joakim@ankeborg.se Joakim von Anka Email: joakim@pluring.se
Jan Erik Moström
Ny version med finesser
db = sqlite3.connect('addresses.db') cursor = db.cursor()
# Set up tables
cursor.execute('create table Person(pid integer primary key, first text, last text)') cursor.execute('create table Email(adr text, belongsto integer)')
def addInfo(first,last,adrList):
cursor.execute("insert into Person(first,last) values (?,?)", (first,last))
id = cursor.lastrowid for adr in adrList:
cursor.execute("insert into Email values (?,?)",(adr,id))
# Populate with data
addInfo('Kalle','Anka',['donald@disney.com','kalle@ankeborg.se']) addInfo('Kajsa','Anka',['kajsa@ankeborg.se'])
addInfo('Alexander','Lucas',['alex@ankeborg.se','lucky@disney.com'])
addInfo('Joakim','von Anka',['joakim@pluring.se','joakim@jva.com','jva@disney.com']) addInfo('Knatte','Anka',['knatte@ankeborg.se','knatte@grongolingarna.org'])
addInfo('Fnatte','Anka',['fnatte@ankeborg.se','fnatte@grongolingarna.org']) addInfo('Tjatte','Anka',['tjatte@ankeborg.se','tjatte@grongolingarna.org']) db.commit()
Jan Erik Moström
# List all email addresses
cursor.execute('select adr from Email') for adr in cursor.fetchall():
print 'Email: %s' % adr
Email: donald@disney.com Email: kalle@ankeborg.se Email: kajsa@ankeborg.se Email: alex@ankeborg.se Email: lucky@disney.com Email: joakim@pluring.se Email: joakim@jva.com Email: jva@disney.com
Email: knatte@ankeborg.se
Email: knatte@grongolingarna.org Email: fnatte@ankeborg.se
Email: fnatte@grongolingarna.org Email: tjatte@ankeborg.se
Email: tjatte@grongolingarna.org
Jan Erik Moström
# List all email address for all people
cursor.execute('''select Person.pid, Person.first, Person.last, Email.adr from Person inner join Email
where Person.pid = Email.belongsto order by Person.pid
''')
previd = -1
for adr in cursor.fetchall():
if previd != adr[0]:
previd = adr[0]
print "%s %s has the following email address(es)" % (adr[1],adr[2]) print '\t%s' % adr[3]
Kalle Anka has the following email address(es) donald@disney.com
kalle@ankeborg.se
Kajsa Anka has the following email address(es) kajsa@ankeborg.se
Alexander Lucas has the following email address(es) alex@ankeborg.se
lucky@disney.com
Joakim von Anka has the following email address(es) joakim@pluring.se
joakim@jva.com jva@disney.com
Knatte Anka has the following email address(es) knatte@ankeborg.se
knatte@grongolingarna.org
Fnatte Anka has the following email address(es) fnatte@ankeborg.se
fnatte@grongolingarna.org
Tjatte Anka has the following email address(es) tjatte@ankeborg.se
Jan Erik Moström
# So let's find out who has an email address that ends in
# '@grongolingarna.org'
cursor.execute('''select Person.first, Person.last from Person inner join Email
where Person.pid = Email.belongsto
and Email.adr like '%@grongolingarna.org’
''')
for adr in cursor.fetchall():
print "%s %s" % (adr[0],adr[1])
Knatte Anka Fnatte Anka Tjatte Anka
# So let's find out who has an email address that ends in
# '@grongolingarna.org'
cursor.execute('''select Person.first, Person.last from Person inner join Email
where Person.pid = Email.belongsto
and Email.adr like '%@grongolingarna.org' order by Person.first
''')
for adr in cursor.fetchall():
print "%s %s" % (adr[0],adr[1])
Fnatte Anka Knatte Anka Tjatte Anka
