PPT slides

scacchicgardenSoftware and s/w Development

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

72 views

Relational Databases

What is a
database
?


a collection of tables of data


each table has


a fixed number of columns


a variable number of rows (or
records
)

Why use a
database
?


allow multiple people to view and modify them


quickly search for data


relate data from multiple tables


designed to make
searching
,
storing

and
updating

large amounts of data easy

Accessing a Database

How do we
access

a database?


use a
database management system (DBMS)


a
DBMS

is a program that we use to manipulate
the database


examples are Microsoft Access, IBM

s Oracle
DB2, MySQL, PostGreSQL,
SQLite



Python

program

SQL


commands

DBMS (pysqlite)

database

Python and
pysqlite

Python has a builtin module

pysqlite,
which we use by importing:



from sqlite3 import dbapi2 as sqlite

Connect

to the database:


con = sqlite.connect(

weather.db

)

Get a
cursor

which is our link to the database:


cur = con.cursor()

After we edit our databse, we must
commit

the changes:


cur.commit()

Close the
cursor

when we are done:


cur.close()

Close the
connection

when we are done:


con.close()


SQL Commands

We run SQL commands in python with:


cur.execute(

sql statement here

)

Creating a table:


cur.execute(
'CREATE

TABLE

Precipitation(City
\

TEXT
,
Snow
REAL
,
Total
INTEGER
,
Days
INTEGER
)')


Name of the table:
Precipitation

Names of the columns:
City
,
Snow
,
Total

and
Days

Type of data in the columns:
TEXT
,
REAL
,
INTEGER
,
INTEGER


Corresponding python types:
str float int int

Precipitation Table Data

Here
are
the
data
files
that
we
mak
e
into
tables
in
our
databases.
pr
ecipitation.txt
City
Sno
w
T
otal
Days
St.John’
s
322.1
1482
217
Charlotteto
wn
338.7
1201
177
Halif
ax
261.4
1474
170
Fredericton
294.5
1131
156
Quebec
337.0
1208
178
Montreal
214.2
940
162
Otta
w
a
221.5
911
159
T
oronto
135.0
819
139
W
innipe
g
114.8
504
119
Re
gina
107.4
364
109
Edmonton
129.6
461
123
Calg
ary
135.4
399
111
V
ancouv
er
54.9
1167
164
V
ictoria
46.9
858
153
Whitehorse
145.2
269
122
Y
ello
wknife
143.9
267
118
geograph
y
.txt
City
Pro
vince
St.John’
s
Ne
wfoundland
Charlotteto
wn
PEI
Halif
ax
No
v
a
Scotia
Fredericton
Ne
w
Brunswick
Quebec
Quebec
Montreal
Quebec
Otta
w
a
Ontario
T
oronto
Ontario
W
innipe
g
Manitoba
Re
gina
Saskatche
w
an
Edmonton
Alberta
Calg
ary
Alberta
V
ancouv
er
BC
V
ictoria
BC
Whitehorse
Y
uk
on
Y
ello
wknife
NWT
temperatur
e.txt
City
A
vgHigh
A
vgLo
w
ColdMonth
ColdA
vgHigh
ColdA
vgLo
w
W
armMonth
W
armA
vgHigh
W
armA
vgLo
w
St.John’
s
8.6
0.8
February
-1.4
-8.7
July
20.2
10.5
Charlotteto
wn
9.5
0.8
January
-3.4
-12.2
July
23.1
13.6
Halif
ax
10.7
1.4
February
-1.5
-10.6
July
23.4
13.2
Fredericton
11.0
-0.6
January
-4
-15.4
July
25.6
12.9
Quebec
9.0
-1.0
January
-7.7
-17.3
July
24.9
13.2
Montreal
10.9
1.2
January
-5.8
-14.9
July
26.2
15.4
Otta
w
a
10.7
0.8
January
-6.3
-15.5
July
26.4
15.1
T
oronto
12.6
5.2
January
-1.3
-7.9
July
26.5
17.6
W
innipe
g
8.1
-3.4
January
-13.2
-23.6
July
26.1
13.4
Re
gina
8.9
-3.8
January
-11
-22.1
July
26.3
11.9
Edmonton
8.7
-1.5
January
-8.2
-17
July
23.0
12.0
Calg
ary
10.3
-2.6
January
-3.6
-15.7
July
23.2
9.5
V
ancouv
er
13.5
6.1
January
5.7
0.1
August
21.7
12.9
V
ictoria
13.9
5.1
January
6.5
0.3
July
21.8
10.7
Whitehorse
4.1
-6.2
January
-14.4
-23.2
July
20.3
7.6
Y
ello
wknife
-0.8
-9.7
January
-23.9
-32.2
July
20.8
12.0
10
More SQL Commands

Delete or
drop

a table


cur.execute(

DROP TABLE

Precipitation

)


Insert a row into a table


cur.execute(

INSERT INTO
Precipitation

VALUES
(
"St. John
\
's",
322.1, 1482, 217
)

)

or


cur.execute(

INSERT INTO
Precipitation

VALUES(
?,?,?,?
)

,
(
"St.
John
\
's", 322.1, 1482, 217))

or


cur.execute(

INSERT INTO
Precipitation

VALUES(
?,?,?,?
)

,
(
city_name, snow, total, days))


variables with values assigned

Retrieving Data from a Table

Two
-
step process:


1.

SELECT

the columns we want to work with:


cur.execute(

SELECT

* FROM

Precipitation

)

or


cur.execute(

SELECT

City, Snow

FROM







Precipitation

)


2.

fetchone()

retrieves the data one row at a time


fetchall()

retrieves all the data


print(cur.fetchone())

or

data = cur.fetchall()


NOTE:
data is retrieved as

tuples

e.g.,

(

St. Johns

, 322.1)