python 数据库书籍_python数据库读书笔记。自己设计·大学排名--数据库实践

一、SQLite 数据库笔记

1.SQLite支持的基本数据类型主要有以下几类：

NULL 类型，取值为 NULL，表示没有或者为空

INTERGER类型，取值为带符号的整数，即可为负整数

REAL类型，取值为浮点数

TEXT 类型，取值是字符串

BLOB类型，是一个二进制的数据块，即字节串，可用于存放纯二进制数据，例如图片

在python中，使用sqlite3创建数据库的连接，当数据库文件不存在时，会新建数据库文件；如果数据库文件已经存在了，则打开数据库文件。

连接对象可以是硬盘上面的数据库文件，也可以是建立在内存中的，在内存中的数据库执行完任何操作后，不需要提交事务的commit。

#使用:memory:标识打开的是内存数据库

import sqlite3

con = sqlite3.connect(":memory:")

#在本地当前目录下创建数据库文件\打开数据库文件-test.db

conn = sqlite3.connect('test.db')

打开数据库时返回的对象conn就是一个数据库连接对象，它可以有以下操作：

commit() --事务提交

rollback() --事务回滚

close() --关闭一个数据库链接

cursor() --创建一个游标

创建一个游标对象：cu

cu = conn.cursor()

关闭一个游标对象

cu.close()

在sqlite3中，所有sql语句的执行都要在游标对象的参与下完成

对于游标对象cu，具有以下具体操作：

execute() --执行一条sql语句

executemany() --执行多条sql语句

close() --游标关闭

fetchone() --从结果中取出一条记录

fetchmany() --从结果中取出多条记录

fetchall() --从结果中取出所有记录

scroll() --游标滚动

2. 创建table

创建表(id,name,gender,age,address,phone)，通过cu.execute(create_table_sql)执行sql语句创建表，如果失败会抛出异常。“table student already exists”，代表已经存在student表，不能再次创建。

create table 表名称(列名1 类型 配置, 列名2 类型 配置, 列名3 类型 配置);

注意，SQL语言是不区分大小写的，create 也可以写成CREATE。另外，每一句SQL语句后面都需要一个;号结尾

示例：

create table contacts (

id integer primary key autoincrement,

name text not null ,

phone text not null default 'unknow');

上面的DDL语句创建了一个叫contacts的表，并且定义了三个列，分别是id、name和phone，并且给每一个列定义了数据类型，分别是integer、text、text，这表明，id只能是一个整数，name和phone只能是字符串。

除了这些，还对每一个列做了一些配置，或者叫约束。

primary key autoincrement 的意思是指将id这个列定义为主键，并且从1开始自动增长，也就是说id这个列不需要人为的手动去插入数据，它会自动增长。

not null 指明这一列不能为空，当你插入数据时，如果不插入name或者phone的值，那么就会报错，无法完成这一次插入。

default 'unknow' default关键字代表设置默认值，这里指定它默认值是字符串'unkonw'，当不插入这一列数据时，默认就是这个值。此处写法是有些多余的，它与not null 一起用是没有意义的，因为not null已经指明这一列必须插入，不可能为null，那就不需要默认值了，当然，此处只是为了演示default的用法

操作步骤

导入模块

连接数据库，返回连接对象

调用连接对象的execute()方法，执行SQL语句，进行增删改的操作，如进行了增添或者修改数据的操作，需调用commit()方法提交修改才能生效；execute()方法也可用于执行DDL语句进行创建表的操作

调用连接对象的cursor()方法返回游标对象，然后调用游标对象的execute()方法执行查询语句，查询数据库

关闭连接对象和游标对象

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

"""Created on Sat May 30 16:33:45 2020@author: Administrator"""# 导入模块

import sqlite3

# 连接数据库，返回连接对象

conn= sqlite3.connect("C:/python练习/test1.db")

# 调用连接对象的execute()方法，执行SQL语句

# (此处执行的是DDL语句，创建一个叫students_info的表)

conn.execute("""create table if not exists students_info (

id integer primary key autoincrement,

name text,

age integer,

address text)""")

# 插入一条数据

conn.execute("insert into students_info (name,age,address) values ('Tom',18,'北京东路')")

# 增添或者修改数据只会必须要提交才能生效

conn.commit()

# 调用连接对象的cursor()方法返回游标对象

cursor=conn.cursor()

# 调用游标对象的execute()方法执行查询语句

cursor.execute("select * from students_info")

# 执行了查询语句后，查询的结果会保存到游标对象中，调用游标对象的方法可获取查询结果

# 此处调用fetchall方法返回一个列表，列表中存放的是元组，

# 每一个元组就是数据表中的一行数据

result=cursor.fetchall()

#遍历所有结果，并打印for row inresult:

print(row)

#关闭

cursor.close()

conn.close()

2. 利用爬虫爬取最好大学网上对于2018年全国各大高校的各项指标的排名及综合状况：

importrequests, csvfrom bs4 importBeautifulSoup

res_univer= requests.get('http://www.zuihaodaxue.com/zuihaodaxuepaiming2017.html')

res_univer.encoding= 'utf-8'bs_univer= BeautifulSoup(res_univer.text, 'html.parser')

list_all_univer= bs_univer.find_all('tr')[1:501]

count=0

list_data=[]

with open('university.csv', 'w', encoding='gbk') as file_1:

writer=csv.writer(file_1)

writer.writerow(['排名','学校名称','省市','总分','生源质量','培养结果','科研规模','科研质量','顶尖成果','顶尖人才','科技服务','成果转化','学生国际化'])for list_a_univer inlist_all_univer:

count+= 1list_data.append(count)

data= list_a_univer.find_all('td')for x in range(1,13):

list_data.append(data[x].text)

writer.writerow(list_data)

list_data= []

查询“广东技术师范学院”的排名和得分信息，我们有下面的代码：

importpandasimportcsvimportsqlite3

conn= sqlite3.connect("db+2019310143128.db")

df= pandas.read_csv('C:/python练习/university.csv',encoding='gbk')

df.to_sql('w2019310143128', conn, if_exists='append', index=False)

cursor=conn.cursor()

cursor.execute('select * from w2019310143128')

all=cursor.fetchall()for line inall:if "广东技术师范学院" inline:print(line)breakcursor.close()

conn.commit()

cursor.close()

结果显示：

广东省的高校的排名和得分情况(以顶尖成果为例)：

importpandasimportsqlite3importpandas

conn= sqlite3.connect("db+2019310143128.db")

df= pandas.read_csv('C:/python练习/university.csv',encoding='gbk')

df.to_sql('w28', conn, if_exists='append', index=False)

cur=conn.cursor()

cur.execute('SELECT * FROM w28')

all=cur.fetchall()for line inall:if "广东" inline:print("{} {} {} {}".format(line[0],line[1],line[2],line[7]))

conn.close()