Bazy danych w Pythonie Jest wiele możliwości wykorzystania baz

Bazy danych w Pythonie
Jest wiele możliwości wykorzystania baz danych w pythonie. Jedną z nich (chyba najprostszą) jest użycie biblioteki
SQLite która od wersji Python 2.5 jest już standardowo dostępna w ramach pytona.
Pomocna na zajęciach może być stron WWW: http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html
Przykład 1. Tworzymy tabele z danymi:
import sqlite3
conn = sqlite3.connect('artysci')
c=conn.cursor()
c=c.execute("""create table Artists (
ArtistID INTEGER PRIMARY KEY,
ArtistName TEXT);""");
c=c.execute("""create table CDs (
CDID INTEGER PRIMARY KEY,
ArtistID INTEGER NOT NULL,
Title TEXT NOT NULL,
Date TEXT);""");
c=c.execute("""insert into Artists (ArtistID,ArtistName) values (NULL,'Peter Gabriel')""")
c=c.execute("""insert into Artists (ArtistID,ArtistName) values (NULL,'Bruce Hornsby')""")
c=c.execute("""insert into Artists (ArtistID,ArtistName) values (NULL,'Lyle Lovett')""")
c=c.execute("""insert into Artists (ArtistID,ArtistName) values (NULL,'Beach Boys')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'So','1984')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'Us','1992')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,'The Way It Is','1986')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,'Scenes from the Southside','1990')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'Security','1990')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,3,'Joshua Judges Ruth','1992')""")
c=c.execute("""insert into CDs (CDID,ArtistID,Title,Date) values (NULL,4,'Pet Sounds','1966')""")
conn.commit()
c = c.execute('select * from Artists')
for row in c:
print row
c.close()
Efekt:
Przykład 2. Przeglądamy dane w bazie danych artystów:
import sqlite3
conn = sqlite3.connect('artysci')
c=conn.cursor()
c=c.execute("""SELECT * FROM CDs
WHERE CDID <=2""")
for row in c:
print row
c.close()
Przykład 3. Przeglądamy dane w bazie danych artystów i wyświetlamy dane dotyczące artystów i ich plyt CD:
import sqlite3
conn = sqlite3.connect('artysci')
c=conn.cursor()
c=c.execute("""SELECT * FROM CDs,Artists
WHERE CDs.ArtistID = Artists.ArtistID""")
for row in c:
print row
c.close()
Przykład 4. Zmieniamy rekordy w tabeli gdzie rok był 1990 na 2000:
import sqlite3
conn = sqlite3.connect('artysci')
c=conn.cursor()
c=c.execute("""UPDATE CDs
SET Date='2000'
WHERE Date='1990'""")
conn.commit()
c=c.execute("""Select * from CDs""")
for row in c:
print row
c.close()
Wykonaj dwiczenia:
1.
2.
3.
4.
5.
6.
7.
select * from CDs;
SELECT Title AS AlbumName FROM CDs;
SELECT Title FROM CDs WHERE Date>=1990 ORDER BY Title;
SELECT Date FROM CDs;
SELECT DISTINCT Date FROM CDs;
SELECT Title FROM CDs GROUP BY ArtistID;
Selecting from 2 tables:
SELECT t1.ArtistName,CDs.Title FROM Artists t1, CDs WHERE
t1.ArtistID=CDs.ArtistID
8. insert into Artists (ArtistID,ArtistName) values (NULL,'Supernatural');
9. UPDATE Artists SET ArtistName ='Santana' WHERE ArtistID=5;
10. insert into CDs (CDID,ArtistID,Title,Date) values (NULL,5,'Supernatural','1999');
11. select * FROM CDs WHERE Title LIKE 'Super%';
12. DELETE FROM CDs WHERE Title LIKE 'Super%';
13. Select * From CDs WHERE Title LIKE 'Super%';