一、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 in result:
    print(row)

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

import requests, csv
from bs4 import BeautifulSoup

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 in list_all_univer:
        count += 1
        list_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 = []

import pandas
import csv
import sqlite3
 
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 in all:
    if "广东技术师范学院" in line:
        print(line)
        break

cursor.close()
conn.commit()
cursor.close()

import pandas
import sqlite3
import pandas
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 in all:
    if "广东" in line:
        print("{} {} {} {}".format(line[0],line[1],line[2],line[7]))
conn.close()

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