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. select * from CDs;
2. SELECT Title AS AlbumName FROM CDs;
3. SELECT Title FROM CDs WHERE Date>=1990 ORDER BY Title;
4. SELECT Date FROM CDs;
5. SELECT DISTINCT Date FROM CDs;
6. SELECT Title FROM CDs GROUP BY ArtistID;
7. 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.
