Python对SQLite 数据库基本操作实例

第一部分：源代码

#!/usr/bin/env python

# -*- coding:utf-8 -*-

#导入sqlite3模块

importsqlite3

#导入os模块

importos

#判断当前目录下是否存在example.db文件，如果存在直接删除

filename ="example.db"

ifos.path.exists(filename) ==True:

os.remove(filename)

else:

pass

#连接example.db数据库文件，如果不存在将直接创建新的example.db文件

conn = sqlite3.connect(filename)

#创建游标

c = conn.cursor()

#创建表、字段

c.execute('''CREATE TABLE COMPANY

(IDINT,

SOFTWARE_NAMETEXT,

SOFTWARE_VERSIONTEXT,

COMPANY_NAMETEXT,

COMPANY_ADDRESSCHAR(50),

PRICEREAL)''')

#插入记录（数据)

c.execute("INSERT INTO COMPANY (ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,

COMPANY_ADDRESS,PRICE) VALUES (1,'abaqus','6.14','XX科技公司','XXX街道XXX号',100)");

c.execute("INSERT INTO COMPANY (ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,

COMPANY_ADDRESS,PRICE) VALUES (2,'ansys','18.0','XX科技公司','XXX街道XXX号',45)");

c.execute("INSERT INTO COMPANY (ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,

COMPANY_ADDRESS,PRICE) VALUES (3,'hypermesh','6.14','XX科技公司','XXX街道XXX号',80)");

c.execute("INSERT INTO COMPANY (ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,

COMPANY_ADDRESS,PRICE) VALUES (4,'UG NX','10.0','XX科技公司','XXX街道XXX号',70)");

#提交事务，保存数据

conn.commit()

#输出数据库数据

cursor = c.execute("SELECT ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,COMPANY_ADDRESS,PRICE from COMPANY")

print("读取数据库的内容，第一次输出！")

forrowincursor:

print("ID = ",row[])

print("SOFTWARE_NAME = ",row[1])

print("SOFTWARE_VERSION = ",row[2])

print("COMPANY_NAME = ",row[3])

print("COMPANY_ADDRESS = ",row[4])

print("PRICE = ",row[5])

print("----------*****----------")

#更新数据库数据，并重新输出

c.execute("UPDATE COMPANY set PRICE = 200 where ID=1")

conn.commit()

cursor = c.execute("SELECT ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,COMPANY_ADDRESS,PRICE from COMPANY")

print("更新数据库采购价，第二次输出！")

forrowincursor:

print("ID = ",row[])

print("SOFTWARE_NAME = ",row[1])

print("SOFTWARE_VERSION = ",row[2])

print("COMPANY_NAME = ",row[3])

print("COMPANY_ADDRESS = ",row[4])

print("PRICE = ",row[5])

print("----------*****----------")

#删除数据库部分内容，并重新输出

c.execute("DELETE from COMPANY where ID=2;")

conn.commit()

cursor = c.execute("SELECT ID,SOFTWARE_NAME,SOFTWARE_VERSION,COMPANY_NAME,COMPANY_ADDRESS,PRICE from COMPANY")

print("删除部分采购信息，第三次输出！")

forrowincursor:

print("ID = ",row[])

print("SOFTWARE_NAME = ",row[1])

print("SOFTWARE_VERSION = ",row[2])

print("COMPANY_NAME = ",row[3])

print("COMPANY_ADDRESS = ",row[4])

print("PRICE = ",row[5])

print("----------*****----------")

#关闭数据库

conn.close()

第二部分：执行结果