连接并创建数据库
conn = sqlite3.connect(database)
其中:
* database的filename存在,连接该数据库;
* database的filename不存在,则以filename创建该数据库,database参数中可指定路径。
创建表
# 常规建表方式
conn = sqlite3.connect(database)
c = conn.cursor()
print ("数据库打开成功")
c.execute('''CREATE TABLE 表名
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
conn.commit()
conn.close()
# 带判断的建表方式----推荐
conn = sqlite3.connect(database)
c = conn.cursor()
print ("数据库打开成功")
c.execute('''CREATE TABLE IF NOT EXISTS 表名
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
conn.commit()
conn.close()
INSERT
插入数据(增)
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )")
conn.commit()
conn.close()
SELECT
操作(查)
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
# 数据保存在cursor对象中,可用for循环进行遍历
cursor = c.execute("SELECT id, name, address, salary from COMPANY")
conn.close()
UPDATE
操作(改)
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
conn.close()
DELETE
操作(删)
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
conn.close()