python3 连接 SQLite

python3 连接 SQLite

1. SQLite简介

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布。
#参考百度百科：https://baike.baidu.com/item/SQLite/375020?fr=aladdin

2. 连接代码

2.1 连接语句

conn = sqlite3.connect('./DB/SQLite自动生成')
# 该语句会自动生成一个数据库文件（如果不存在的话）

2.2 CREATE操作Demo

import sqlite3

conn = sqlite3.connect('./DB/SQLite自动生成')
c = conn.cursor()
try:
    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")
except sqlite3.OperationalError as operationalError:
    print("创建表失败")
    print("sqlite3.OperationalError",end="")
    print(operationalError)
finally:
    conn.commit()
    conn.close()

2.3 INSERT操作Demo

import sqlite3

conn = sqlite3.connect('./DB/SQLite自动生成')
c = conn.cursor()

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")
conn.close()

2.4 SELECT操作Demo

import sqlite3

conn = sqlite3.connect('./DB/SQLite自动生成')
c = conn.cursor()

cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print("ID = ", row[0],end="")
   print("   NAME = ", row[1],end="")
   print("   ADDRESS = ", row[2],end="")
   print("   SALARY = ", row[3])

col_name_list = [tuple[0] for tuple in cursor.description]
print(col_name_list)

print("Operation done successfully")
conn.close()

2.5 UPDATE操作Demo

import sqlite3

conn = sqlite3.connect('./DB/SQLite自动生成')
c = conn.cursor()

c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
print("Total number of rows updated :", conn.total_changes)

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

print("Operation done successfully")
conn.close()

2.6 DELETE操作Demo

import sqlite3

conn = sqlite3.connect('./DB/SQLite自动生成')
c = conn.cursor()

c.execute("DELETE from COMPANY where ID=8;")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

print("Operation done successfully")
conn.close()

3. 自制的SQLite3Helper

请在资源页下载：跳转链接
使用方法：

def TestUnit():
    # Conncet Test
    helper=SQLite3Helper('./DB/SQLite自动生成.dat')
    # CreateNewTable Test
    helper.CreateNewTable('''CREATE TABLE COMPANY(
                     ID        INT PRIMARY KEY     NOT NULL,
                     NAME      TEXT                 NOT NULL,
                     AGE       INT                  NOT NULL,
                     ADDRESS   CHAR(50),
                     SALARY    REAL);''')
    # InsertIntoTable Test
    helper.InsertIntoTable("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")
    helper.InsertIntoTable("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
    helper.InsertIntoTable("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
    helper.InsertIntoTable("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
    # Select Test
    helper.Select("SELECT id, name, address, salary  from COMPANY",True)
    # Update Test
    helper.Update("UPDATE COMPANY set SALARY = 999.99 where ID=1")
    # Delete Test
    helper.Delete("DELETE from COMPANY where ID=4;")
    helper.Select("SELECT id, name, address, salary  from COMPANY",False)

if __name__=="__main__":
    TestUnit()