SQLite3
Create a new database or open the existing database (on the command line)
> sqlite3 FirstDB.db
Then you will go to the sqlite3 with database opened (indicated by “sqlite>")
Create a table:
1
2
3
4
5
6
|
sqlite> CREATE TABLE Person (
...> firstName TEXT(30) NOT NULL, ## MySQL uses VARCHAR instead of TEXT
...> lastName TEXT(30) NOT NULL,
...> birthYear INTEGER,
...> PRIMARY KEY (firstName, lastName)
...> );
|
Add record to the table:
INSERT INTO Person (firstName, lastName, birthYear) VALUES ("See", "Ferry", "1990");
INSERT INTO Person (firstName, lastName, birthYear) VALUES ("Mountain", "Bike", "1966");
Update record:
UPDATE Person SET birthYear=1980 WHERE lastName='Bike';
Query the table:
SELECT firstName from Person WHERE birthYear > 1988;
Add new column to table:
sqlite> ALTER TABLE Person
...> ADD height INT;
Delete a record or all records:
DELETE FROM Person WHERE lastName='Ferry';
DELETE FROM Person;
Show headers for tables and show as column:
sqlite> .headers ON
sqlite> .mode column
Show all records in a table:
SELECT * FROM Person WHERE 1;
Remove duplicate rows (identical in firstName, lastName, birthYear):
DELETE FROM Person WHERE rowid NOT IN (SELECT min(rowid) FROM Person GROUP BY firstName, lastName, birthYear);
List all tables or databases:
sqlite> .tables
sqlite> .databases
Remove a table:
DROP TABLE Person;
Quit sqlite3
sqlite> .quit
Run commands from a script (i.e. sql):
sqlite3 Person < Operations.sql
Create database in Python
See the turorial here.
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
|
# Create a connection
def create_connection(db_file):
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return None
# Cursor and execute the command
def create_table(conn, create_table_sql)
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
# Create a main() function to CREATE TABLE
def main():
database = "/Users/zl3/Documents/FirstDB.db"
create_table = """CREATE TABLE IF NOT EXISTS Person (
id integer PRIMARY KEY,
firstName text NOT NULL,
lastName text NOT NULL,
birthYear ineger
);"""
# create a database connection
conn = create_connection(database)
if conn is not None:
# create table
create_table(conn, create_table)
else:
print("Error! Cannot create the database connection.")
# Execute the main() function
if __name__ == '__main__':
main()
|
### Query and modify database from Python
Script like this:
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
|
#! /usr/bin/env python
# -*- coding: utf-8 -*- # To accept special characters
import sqlite3
# establish a connection
connection = sqlite3.connect("/Users/zl3/Documents/FirstDB.db")
# hold a cursor
cursor = connection.cursor()
## new data in a list:
new_person = [
("Double-decker", "Bus", 2008),
("Electric", "Bike", 2006),
]
## insert into the table
for p in new_person:
format_str = """INSERT INTO Person (firstName, lastName, birthYear)
VALUES ("{first}", "{last}", "{birthyear}");"""
sql_command = format_str.format(first=p[0], last=p[1], birthyear=p[2])
cursor.execute(sql_command)
## Fetch all remaining records
cursor.execute("SELECT * FROM Person")
print("Fetchall:")
all_records = cursor.fetchall()
for r in all_records:
print(r)
## Fetch the next record
cursor.execute("SELECT * FROM Person")
print("\nFetch one:")
one_record = cursor.fetchone()
print(one_record)
# close the cursor
cursor.close()
# Discard changes
# connection.rollback()
# Save the changes and close the connection:
connection.commit()
connection.close()
|
