import sqlite3
# create database
conn = sqlite3.connect('test.db') #不存在就创建后再打开
print ("Opened database successfully")
conn.execute('''''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print ("Table created successfully")
conn.close()
# inset data into database
conn = sqlite3.connect('test.db')
print ("Opened database successfully")
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()
print ("Records created successfully")
conn.close()
# select from database
conn = sqlite3.connect('test.db')
print ("Opened database successfully")
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print ("ID = %d" % row[0])
print ("NAME = %s" % row[1])
print ("ADDRESS = %s" % row[2])
print ("SALARY = %d" % row[3])
print("\n")
print ("Operation done successfully")
conn.close()