FazBrowse GitHub Viewer
|
Trending
|
URL:
|
Home
Tools:
[Download Repo ZIP]
[View Raw Code]
[Original HTTPS Page]
comp0034_SQL/create_db_sqlite.py at master · UCLComputerScience/comp0034_SQL · GitHub
UCLComputerScience
comp0034_SQL
Repository navigation
Code
Issues
Pull requests
2
(2)
Actions
Projects
Security and quality
Insights
Expand file tree
Breadcrumbs
comp0034_SQL
/
create_db_sqlite.py
Copy path
More file actions
More file actions
Latest commit
History
History
History
52 lines (43 loc) · 1.76 KB
Breadcrumbs
comp0034_SQL
/
create_db_sqlite.py
Copy path
File metadata and controls
52 lines (43 loc) · 1.76 KB
Raw
Copy raw file
Download raw file
Open symbols panel
Edit and raw actions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import
sqlite3
# Step 1: Create a connection object that represents the database
conn
=
sqlite3
.
connect
(
'address_sqlite.db'
)
# Step 2: Create a cursor object
c
=
conn
.
cursor
()
# Step 3: Create the person and address tables
c
.
execute
(
'''
CREATE TABLE person
(person_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL)
'''
)
c
.
execute
(
'''
CREATE TABLE address
(address_id INTEGER PRIMARY KEY,
street_number TEXT,
street_name TEXT,
postcode TEXT NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(person_id) REFERENCES person(person_id))
'''
)
# Insert data for a person using values from variables
# You could use: c.execute("INSERT INTO person VALUES('Jo', 'Bloggs')")
sql
=
"INSERT INTO person (first_name, last_name) VALUES (?, ?)"
values
=
(
'Jo'
,
'Bloggs'
)
c
.
execute
(
sql
,
values
)
# Insert the address for the person
# If you know the rowid you can use: c.execute("INSERT INTO address VALUES(1, 'My Road', 'SE1 9PZ', 1)")
# Since we just inserted Jo this was the last insert so we can get the id of the last inserted row using c.lastrowid:
sql
=
"INSERT INTO address (street_number, street_name, postcode, person_id) VALUES (?, ?, ?, ?)"
values
=
(
12
,
'My Road'
,
'SE1 9PZ'
,
c
.
lastrowid
)
c
.
execute
(
sql
,
values
)
# Insert multiple rows cursor.executemany()
sql
=
"INSERT INTO person (first_name, last_name) VALUES (?, ?)"
values
=
[(
'David'
,
'Coverdale'
),
(
'Robert'
,
'Plant'
),
(
'Joe'
,
'Elliott'
)]
c
.
executemany
(
sql
,
values
)
# Step 5: Save (commit) the changes
conn
.
commit
()
# Step 6 (optional): Close the connection if you are done with it
# Be sure any changes have been committed or they will be lost.
conn
.
close
()
Back
|
FazBrowse Home
|
New Git URL