sqlit3基本操作--python

 

示例：

#导入数据库驱动
import sqlite3
import os

#打开数据库，不存在则创建数据库
conn = sqlite3.connect('test.db')
print("Opened database successfully")
c = conn.cursor()


#创建表，表名字COMPANY
c.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")

#插入信息
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

#提交信息
conn.commit()
print("Records created successfully")


#获取数据库内容
cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print( "ID = {0}".format(row[0]))
   print( "NAME = {0}".format(row[1]))
   print( "ADDRESS = {0}".format(row[2]))
   print( "SALARY = {0}".format(row[3]))
print("Operation done successfully")

#更新数据库中内容。注意更新完数据一定要提交
c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
print("Total number of rows updated :{0}".format(conn.total_changes))
cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print( "ID = {0}".format(row[0]))
   print( "NAME = {0}".format(row[1]))
   print( "ADDRESS = {0}".format(row[2]))
   print( "SALARY = {0}".format(row[3]))
print("Operation done successfully")


#删除条目. 注意删除条目后要提交
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print("Total number of rows deleted :{0}".format(conn.total_changes))
cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print( "ID = {0}".format(row[0]))
   print( "NAME = {0}".format(row[1]))
   print( "ADDRESS = {0}".format(row[2]))
   print( "SALARY = {0}".format(row[3]))
print("Operation done successfully")





conn.close()

if os.path.exists("test.db"):  # 如果文件存在
    # 删除文件，可使用以下两种方法。
    os.remove("test.db")
else:
    print('no such file')  # 则返回文件不存在

 

 

参考资料：

搬移菜鸟教程：https://www.runoob.com/sqlite/sqlite-python.html

c语言版：https://blog.youkuaiyun.com/nan_lei/article/details/84344003

sqlit快速入门：https://www.yiibai.com/sqlite/quick-start.html