当前位置:Gxlcms > 数据库问题 > Python 之 Sqlite3数据库

Python 之 Sqlite3数据库

时间:2021-07-01 10:21:17 帮助过:10人阅读

 

一、数据库(database)简介

  表是数据库中存放关系数据的集合,一个数据库里面通常都包含多个表,表和表之间通过外键关联。

  因为Python的数据库模块有统一的接口标准,所以数据库操作都有统一的模式。要操作关系数据库,大致是以下几步(假设数据库模块名称为db):

  1. 用db.connect连接数据库,假设得到的对象为conn
  2. 如果该数据库操作不需要返回结果,就直接用conn.execute查询,根据数据库事务隔离级别的不同,可能修改数据库需要conn.commit
  3. 如果需要返回查询结果则用conn.cursor创建游标对象cur, 通过cur.execute查询数据库,用cur.fetchall/cur.fetchone/cur.fetchmany返回查询结果。根据数据库事务隔离级别的不同,可能修改数据库需要conn.commit
  4. 关闭cur,conn

 

二、SQLite使用

(1)SQlite数据库的导入

1 import sqlite3

 

(2)创建数据库/打开数据库

1 conn = sqlite3.connect("test.db")

  在调用connect函数的时候,指定数据库名称,如果指定的数据库存在就直接打开这个数据库,如果不存在就新创建一个再打开。

 

(3)数据库连接对象

  打开数据库时返回的对象conn就是一个数据库连接对象,有以下几种基本操作:

 1 #创建一个Cursor游标
 2 cur = conn.cursor()
 3 
 4 #执行一条SQL语句,创建user表
 5 cur.execute(create table user (id varchar(20) primary key, name varchar(20)))
 6 
 7 #执行一条SQL语句,插入一条记录
 8 cur.execute(insert into user (id, name) values (\‘1\‘, \‘Michael\‘))
 9 
10 #获得插入的行数
11 cur.rowcount
12 
13 #执行查询语句
14 cursor.execute(select * from user where id=?, (1,))
15 #获得查询结果
16 values = cur.fetchall()               #如果使用cur.fetchone()则首先返回列表中的第一项,再次使用,则返回第二项,依次下去
17 
18 #关闭cur
19 cur.close()
20 
21 #提交事务
22 conn.commit()
23 
24 #关闭connection连接
25 conn.close()

  使用Cursor游标对象执行insert,update,delete语句时,执行结果由rowcount返回影响的行数,就可以拿到执行结果。

  使用Cursor游标对象执行select语句时,通过fetchall()可以拿到结果集。结果集是一个list,每个元素都是一个tuple,对应一行记录。

  如果SQL语句带有参数,那么需要把参数按照位置传递给execute()方法,有几个?占位符就必须对应几个参数。另外一种方法是使用字符串格式化format()方法。

以上为SQLite数据库的常用的基本操作,具体用法请参阅SQLite的官方网站。SQLite数据库菜鸟教程

 

三、SQLite数据库实践

(1)将上次爬取得到的2017中国最好大学排名.csv文件以db格式文件写入数据库

 1 #!D:/Python/Python.exe
 2 # -*- coding: utf-8 -*-
 3 import sqlite3 as sql
 4 
 5 #获取csv文件的数据
 6 def GetData(Csv_Path):
 7     with open(Csv_Path, "r", encoding="utf-8") as f:
 8         lines = f.readlines()
 9         Data = lines[1:]
10         Headers = lines[0].replace(\n, ‘‘)
11         Headers = Headers.split(,)
12     return Data, tuple(Headers)
13 
14 #创建数据库
15 def CreateDB(DB_Path, Headers, Tab_name):
16     try:
17         conn = sql.connect(DB_Path)
18         cur = conn.cursor()
19         cur.execute(‘‘‘CREATE TABLE {0} {1}‘‘‘.format(Tab_name, Headers))
20         print("Table created successfully")
21         return conn, cur
22     except:
23         print("Created failed")
24 
25 #将数据导入数据库
26 def Import(Csv_Path, DB_Path, Tab_name):
27     Data, Headers = GetData(Csv_Path)
28     Conn, Cur = CreateDB(DB_Path, Headers, Tab_name)
29 #为了之后的排序工作, 以下进行了数据处理
30     for row in Data:
31         row = row.replace(, 0)
32         row = row.replace(\n, ‘‘)
33         row = tuple(row.split(,))
34         Cur.execute("INSERT INTO {0} VALUES {1}".format(Tab_name, row))
35     print("Data import successful")
36     Conn.commit()                  #提交事务
37     Cur.close()                    #关闭游标 
38     Conn.close()                   #关闭数据库连接
39 
40 if __name__ == "__main__":
41     Csv_Path = input("请输入需要导入的csv文件的路径或名称:")
42     DB_Name = input("请输入数据库的名称:")
43     Tab_name = input("请输入数据库的表名称:")
44     Import(Csv_Path, DB_Name, Tab_name)
46

 

将结果用SQLite Studio呈现:

技术图片

技术图片

 

 SQLite Studio是一款 Sqlite数据库可视化工具,使用非常简单,想要了解的话这里给一个我参考的教程。(SQLite Studio使用教程)

 

(2)查询学校的数据

1 import sqlite3 as sql
2 Conn = sql.connect("db2019310143115.db")
3 Cur = Conn.execute("SELECT * from Rank where 学校名称 = ?", (广东技术师范学院,))          #这句代码是产生一个指定位置的cursor游标
4 row = Cur.fetchone()
5 print(row)
6 Cur.close()
7 Conn.close()

  这里只是单纯执行一句sql语句实现查询,并未整合在上面的代码。值得一提的是connection.execute()与cursor.execute()不相同,前者是产生一个游标,后者是直接操作游标。

 

结果为:

技术图片

 

 

(3)按照要求查询并显示广东省的学校的排名和成果转化

1 1 import sqlite3 as sql
2 2 Conn = sql.connect("db2019310143115.db")
3 3 Cur = Conn.execute("SELECT 学校名称, 成果转化(技术转让收入·千元) FROM Rank order by 成果转化(技术转让收入·千元) desc")          #执行sql的order by语句进行排序, desc为倒序, 默认为顺序
4 4 for i in Cur.fetchall():
5 5     print(i)
6 6 Cur.close()
7 7 Conn.close()

 

一部分结果为(左边是学校名称,右边是成果转化的数据)

技术图片

 

 

(4)将广东省(或全国)的学校的排名和数据存为一个新表,并且使用一个算法,综合每个因素进行总排名

  

  由于这一要求并未完成,我仅把上面的功能整合在一起,给出代码:

技术图片
 1 #!D:/Python/Python.exe
 2 # -*- coding: utf-8 -*-
 3 import sqlite3 as sql
 4 
 5 #获取csv文件的数据
 6 def GetData():
 7     Csv_Path = input("请输入csv文件的路径或名称:")
 8     with open(Csv_Path, "r", encoding="utf-8") as f:
 9         lines = f.readlines()
10         Data = lines[1:]
11         Headers = lines[0].replace(\n, ‘‘)
12         Headers = Headers.split(,)
13     return Data, tuple(Headers)
14 
15 #创建新表
16 def CreateDB(Headers, Cur):
17     Tab_name = input("请输入表名称:")
18     try:
19         Cur.execute(‘‘‘CREATE TABLE {0} {1}‘‘‘.format(Tab_name, Headers))
20         print("Table created successfully.")
21     except:
22         print("Creation failed.")
23 
24 #将数据导入数据库
25 def Import(Data, Cur):
26     Tab_name = input("请输入表的名称: ")
27 #为了之后的排序工作, 以下进行了数据处理
28     for row in Data:
29         row = row.replace(, 0)
30         row = row.replace(\n, ‘‘)
31         row = row.split(,)
32         for i in range(len(row)):
33             try:
34                 row[i] = eval(row[i])
35             except:
36                 continue
37         Cur.execute("INSERT INTO {} VALUES {}".format(Tab_name, tuple(row)))                #执行sql语句, 写入数据
38     print("Data imported successfully.")
39 
40 #查询数据
41 def Search(Headers, Cur):
42     Tab_name = input("请输入表的名称: ")
43     School = input("请输入想要查找的学校: ")
44     Cur.execute("SELECT * FROM {} WHERE 学校名称 = ?".format(Tab_name), (School,))
45     Info = Cur.fetchone()
46     for i in range(len(Headers)):
47         print("{}: {}".format(Headers[i], Info[i]))
48 
49 #排序
50 def Sort(Cur):
51     Tab_name = input("请输入表的名称: ")
52     Col = input("请输入想要按照哪列进行排序(默认倒序): ")
53     Cur.execute("SELECT 学校名称, 成果转化(技术转让收入·千元) FROM {} order by {} desc".format(Tab_name, Col))
54     for i in Cur.fetchall():
55         print(i)
56 
57 #数据库功能
58 def Function(Data, Headers):
59     DB_Path = input("请输入数据库的路径或名称:")
60     try:
61         Conn = sql.connect(DB_Path)
62         Cur = Conn.cursor()
63         print("Connect completed.")
64     except:
65         print("Connection fail.")
66     else:
67         while True:
68             Choi = input("请选择你想要使用的功能(1、创建新表 2、插入数据 3、查询 4、排序 0、退出):")
69             if Choi == "0":
70                 Cur.close()
71                 Conn.close()
72                 print("Database closed successfully.")
73                 break
74             elif Choi == "1":
75                 CreateDB(Headers, Cur)
76             elif Choi == "2":
77                 Import(Data, Cur)
78                 Conn.commit()
79             elif Choi == "3":
80                 Search(Headers, Cur)
81             elif Choi == "4":
82                 Sort(Cur)
83             else:
84                 print("Input Error!")
85 
86 if __name__ == "__main__":
87     Data, Headers = GetData()
88     Function(Data, Headers)
View Code

 

未完待续...

  

 

Python 之 Sqlite3数据库

标签:var   failed   connect   文件   根据   事务   header   其它   arch   

人气教程排行