Repository navigation
Expand file tree
/
Copy pathnormalise.py
More file actions
47 lines (35 loc) · 1.48 KB
/
Copy pathnormalise.py
File metadata and controls
47 lines (35 loc) · 1.48 KB
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
import sqlite3
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
cursor.execute("""CREATE TABLE languages
(id INTEGER PRIMARY KEY AUTOINCREMENT,
language_code VARCHAR(2))""")
cursor.execute("""INSERT INTO languages (language_code)
VALUES ('de')""")
cursor.execute("""INSERT INTO languages (language_code)
VALUES ('en')""")
cursor.execute("""CREATE TABLE "temp" (
"id" INTEGER,
"title" TEXT,
"language_code" INTEGER REFERENCES languages(id),
"language" TEXT,
"author" TEXT,
"license" TEXT,
"release_date" DATE,
PRIMARY KEY("id" AUTOINCREMENT)
)""")
cursor.execute("""INSERT INTO temp (title,language,author,license,release_date)
SELECT title,language,author,license,release_date FROM books""")
cursor.execute("""UPDATE temp
SET language_code = 1
WHERE language = 'de'""")
cursor.execute("""UPDATE temp
SET language_code = 2
WHERE language = 'en'""")
# Only SQLite ≥ 3.35.0 allows DROP COLUMN;
# Only Python versions ≥ 3.8 released after 27 April 2021 will receive these or
# newer SQLite versions.
cursor.execute("""ALTER TABLE temp DROP COLUMN language""")
cursor.execute("""DROP TABLE books""")
cursor.execute("""ALTER TABLE temp RENAME TO books""")
conn.commit()