【python爬虫系列】7.SQLALchemy与MYSQL数据库基础

最新推荐文章于 2024-05-10 18:02:59 发布

ZEVIN LI

最新推荐文章于 2024-05-10 18:02:59 发布

阅读量731

点赞数 1

文章标签： python mysql 大数据

本文链接：https://blog.csdn.net/AI_LINNGLONG/article/details/104544352

版权

第七节：SQLALchemy与MYSQL数据库基础

MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。
Python WEB开发界主力一Flask。使用SQLAlchemy进行数据库开发。使用ORM是大势所趋。

7.1.MySql
数据库(Database) 是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有-一个或多个不同的API用于创建，访问管理，搜索和复制所保存的数据。CURD。

数据库的三大范式
范式内容
第一范式
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第二范式第二范式需要确保数据库表中的每一列都和主键相关而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
第三范式第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关，关系型数据库，是建立在关系关系模型的基础上的数据库，借助于集合、代数、等数学概念和方法来处理数据库中的数据。

特点：
数据以表格的形式出现
每行/为各种记录名称
每列/为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成 database
安装方法：

在这里插入图片描述

接下来我们创建一个数据库，供我我们接下来学习使用，以此输入我们的指令

在这里插入图片描述

7.2.SQLALchemy与MYSQL数据库
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
安装模块：

 pip install sqlalchemy 
pip install pymysql

ORM方法论基于三个核心原则：
名称作用
简单以最基本的形式建模数据
传达性数据库结构被任何人都能理解的语言文档化
精确性基于数据模型创建正确标准化了的结构
根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
‘数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名’
mysql+pymysql://😡/[?]
mysql+mysqldb://😡[:]/

2.7版本使用mysqldb 3.5版本使用pymysql

连接数据库的常用样本代码：

from sqlalchemy import create_engine

engine = create_engine(
	"mysql+pymysql://root:123456@127.0.0.1:3306/test",# （里面的 root 要填写你的密码）,注意：mysql+pymysql 之间不要加空格
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5, # 超过连接池大小之后，外最多可以创建的链接 pool_size = 10, # 连接池大小
	echo = True, # 调试信息展示
)

不报错就说明我们成功连接了

1）数据库能存储的类型

SQLAlchemy	   Python
Text	      Long str
Boolean	      bool
BigInteger	  int
Date	      Datetime.data
DateTime	  Datetime.datetime
Float	      float
String	      str

常用数据类型
字段：Column
索引：Index
表：Table
导入 DateTime 字段，default ，不是执行结果，不需要括号。
创建一个table的方式：

test=Table('test',metadata,          Column('id',Integer(),primary_key=True,autoincrement=True),          Column('name',String(255)),          Column('data',DateTime(),default=datetime.now,onupdate=datetime.now),          Column('main',Boolean(),default=False),)
创建完我们的这个表之后，就可以在命令中（也就是已经启动 MySql）输入：
desc test;
虽然，写的是 Python 代码，但是我们的 ORM 会帮我转换的。

完整：

# 连接数据库的样板代码
from sqlalchemy import create_engine,MetaData,Table
from sqlalchemy import Column,String,Integer
import datetime

def  my_dabase_big():
  def my_database():
    engine = create_engine(
      "mysql+pymysql://root:123456@127.0.0.1:3306/test",# （里面的 root 要填写你的密码）,注意：mysql+pymysql 之间不要加空格
      # "mysql + pymysql://root:root@localhost/test",
      max_overflow = 5, # 超过连接池大小之后，外最多可以创建的链接
      pool_size = 10, # 连接池大小
      echo = True, # 调试信息展示
    )

  my_database()
my_dabase_big()
metadata = MetaData() # 取得元素据，介绍数据库
test=Table('test',metadata,
          Column('id',Integer(),primary_key=True,autoincrement=True),
          #  Column：字段，我们可以存储各种字段,
          #  第一个就是我们的字段名称（取个名字）
          #  第二个是类型（数据类型）
          #  第三个是对这个字段的描述（例如第一个是主键，我就 True，自动增长 autoincrement,也就是我每增加一条信息，他的 ID 就增加 1
          Column('name',String(255)),
           # 接下来就是我们的第一个字段， name，后面定义它最长 255 个字节
          Column('data',DateTime(),default=datetime.now,onupdate=datetime.now),
          #  第二个我们存储日期，然后，使用 default 默认就是我们现在的时间
          Column('man',Boolean(),default=False),
           # man 是不是男性，（当然添加其他的也是可以的），使用了 布尔值(Bookean()) 默认不是男性 default=False
           )

conn = engine.connect() # 获取一个连接
a = conn.begin()  # 开始一个事物

然后，接下来。我们就开始执行

ry:
  # 我们在这个事物中的表：test 插入入一个数据，也就是表中的 ：name下面的数据插入 lol3
  conn.execute(test.insert(),{'name':'lol3'})
  print(1/0) # 这里我们故意让代码出错去执行 rollback()
  a.cmmit() # 最后提交即可

except:
  a.rollback()
#   我们来重点说一下这个事物回滚：
# 在上面的 try:...except:...中的代码，在我们故意出错之前是正常执行了在数据库表中插入的操作。
# 但这个代码出错的时候就回滚操作，那这个回顾就会撤销你刚刚所作出来的修改（或者说操作回退，就是类似于没有执行。譬如：文本操作中的 Control + Z）的操作。
# 因为，我（程序）已经发生错误，保证数据库表的完整性，类似于文件的开启就要有关闭（保证文件数据的完整性一个意思）

2）事物（每执行一次操作的session）
一般来说，事物是必须满足4个条件（ACID）
原子性（每个事物插入数据都是很小的事件）
一致性（更改完成同步，改动一致）
隔离性（我和你的操作不一样）
持久性（改变是永久的不是暂时的）
事物用 BEGIN、ROLLBACK、COMMIT 来实现：
BEGIN 开始一个事物
ROLLBACK 事物回滚
COMMIT 事物确认
3）索引
数据库创建索引能够大大提高系统性能。
一通过创建唯一的索引，可以确保数据库表中每一行数据的唯一性；
二可以大大加快数据的检索速度，这也是创建索引的最主要原因；
三可以加速表和表之间的连接，
特别是在实现数据的参考完整性方面特别有意义；
四在使用分组和排序子句进行数据检索时，
同样可以显示著的减少查询中查询中分组和排序的时间
五通过使用索引，可以在查询的过程中，
使用优化隐藏器，提高系统的性能。
4）基本操作
操作方法：execute,update,insert,select,delete,join等
1.创建表结构，链接test数据库（create database test）

# 连接数据库的样板代码
from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer,DateTime,Boolean


engine = create_engine(
  "mysql+pymysql://root:123456@127.0.0.1:3306/test",# （里面的 root 要填写你的密码）,注意：mysql+pymysql 之间不要加空格
  # "mysql + pymysql://root:root@localhost/test",
  max_overflow = 5, # 超过连接池大小之后，外最多可以创建的链接
  pool_size = 10, # 连接池大小
  echo = True, # 调试信息展示
)


metadata = MetaData() # 取得元数据，介绍数据库
# 定义表
user = Table('user',metadata,
             # 数据库表名称，元素据
             Column('id',Integer,primary_key=True,autoincrement=True),
             Column('name',String(10)))
metadata.create_all(engine) # 创建数据表

搜索数据库，发现表格已创建：

3 增删改查数据（方法一原生语句）
利用数据库原生语句，建议有数据库基础的使用

INSERT INTO TABLE (KEY1,KEYA) VALUES (VALUE1,VALUE2);  # 增加语句
UPDATE TABLE SET KEY=VALUE, KEY=VALUE WHERE···;          # 修改语句
SELECT * FROM TABLE;   # 查询语句
DELETE FROM TABLE WHERE ···;     # 删除语句

3.1增加数据：

engine.execute("insert into user (name) values ('zevin li')")

3.2更新修改

engine.execute("update user set name='python' where id = 1")

3.3查询

res = engine.execute("select * from user")
for i in res:
	print(i)
输出：
(1, 'python')

3.4删除：

res = engine.execute("delete from user where id=1")

4增删改查数据（方法二利用表结构）
此方法利用封装好的方法，避免写复杂的底层的 mysql 语句
首先我们新建一个user_tabelss的表：

metadata = MetaData() # 获得元数据，介绍数据库

# 定义表
metadata = MetaData() # 获得元数据，介绍数据库

# 定义表
user_tables = Table('user_tabless', metadata,
                   Column("id", Integer, primary_key=True,autoincrement=True),
                   Column("name",String(10)))
metadata.create_all(engine) # 创建表

4.1增加数据：

conn = engine.connect() # 获取一个连接
conn.execute(user_tabless.insert(),{"name":"python"})  ##增加数据
conn.close()# 关闭连接  #关闭，防止内存溢出

4.2修改数据：

conn = engine.connect() # 获取一个连接
conn.execute(user_tabless.update().where(user_tabless.c.id==1).values(id=2))   #修改
conn.execute(user_tabless.update().where(user_tabless.c.name=="python").values(name="c"))
conn.close()# 关闭连接  #关闭，防止内存溢出

4.3查找数据，先导入select模块：

from sqlalchemy import Column,String,Integer,DateTime,Boolean,select
conn = engine.connect() # 获取一个连接
res = conn.execute(select([user_tabless.c.name]))    #查询
print(res.fetchall())
conn.close()# 关闭连接  #关闭，防止内存溢出
输出: [('c',)]
删除数据：
conn = engine.connect() # 获取一个连接
conn.execute(user_tabless.delete().where(user_tabless.c.id==2))
conn.close()# 关闭连接  #关闭，防止内存溢出

4增删改查数据（方法三集成ORM类操纵数据库推荐学会）
导入包：在这里插入图片描述

4.0创建表（新方法利用class）：

from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import  declarative_base 
# 导入我们的基类，我们创建的类都是以他为基础
# sessionmaker 用于代替 conn 也就是 engine.connect()
# conn = engine.connect() 获取一个连接

# 连接数据库
engine = create_engine(
  "mysql+pymysql://root:123456@127.0.0.1:3306/test",# （里面的 root 要填写你的密码）,注意：mysql+pymysql 之间不要加空格
  # "mysql + pymysql://root:root@localhost/test",
  max_overflow = 5, # 超过连接池大小之后，外最多可以创建的链接
  pool_size = 10, # 连接池大小
  echo = True, # 调试信息展示
)


Base = declarative_base()
class Host(Base):
  # 表名为 hosts
  __tablename__='hosts'  #建议写 也可以不写
  # 表结构
  # primary_key 等于主键 自动增长
  # unique 唯一 别人和你一样就查不进去 比如你注册时候名字重复不让你用
  # nullable 非空  不容许提交空
  id = Column(Integer,primary_key=True,autoincrement=True)
  hostname = Column(String(64),unique=True,nullable=False)
  ip_addr = Column(String(128),unique=True,nullable=False)
  port = Column(Integer, default=22)
  title = Column(String(200))


Base.metadata.create_all(engine) # 创建表

首先用 sessionmaker 绑定 engine 
之后实例化绑定 session
用 session 进行 CURD >>> Create、Update、Read、Delete
接下来就是 增删改查
值得一提的是，无论是增删改查，最后都不要忘记.commit()
4.1增加数据：
#集成ORM类操纵数据库 
from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import  declarative_base # 导入我们的基类，就是我们创建的类都是以他为基础
# sessionmaker 用于代替 conn 也就是 engine.connect()
# conn = engine.connect() 获取一个连接

# 连接数据库
engine = create_engine(
  "mysql+pymysql://root:123456@127.0.0.1:3306/test",# （里面的 root 要填写你的密码）,注意：mysql+pymysql 之间不要加空格
  # "mysql + pymysql://root:root@localhost/test",
  max_overflow = 5, # 超过连接池大小之后，外最多可以创建的链接
  pool_size = 10, # 连接池大小
  echo = True, # 调试信息展示
)


Base = declarative_base()
class Host(Base):
  # 表名为 hosts
  __tablename__='hosts'  #建议写 也可以不写
  # 表结构
  # primary_key 等于主键 自动增长
  # unique 唯一 别人和你一样就查不进去 比如你注册时候名字重复不让你用
  # nullable 非空  不容许提交空
  id = Column(Integer,primary_key=True,autoincrement=True)
  hostname = Column(String(64),unique=True,nullable=False)
  ip_addr = Column(String(128),unique=True,nullable=False)
  port = Column(Integer, default=22)  #default 默认填充 就如网站注册你不填给你默认名字
  title = Column(String(200))


#Base.metadata.create_all(engine) # 创建表


if __name__ == '__main__':
  try:
    Session = sessionmaker(bind=engine)  #bind 监听哪一个引擎 这里是engine
    sess = Session() # 创建实例
    '''h = Host(hostname='test1',ip_addr="127.0.0.1")
    h2 = Host(hostname='test2',ip_addr="192.168.0.1",port=8000)
    h3= Host(hostname='test3',ip_addr="192.168.0.3",port=8080)
    sess.add(h) # 每次添加一个
    sess.add_all([h2,h3]) # 每次添加多个
    sess.commit() # 事物提交，必须要有'''
  except:
    print("Error!")

4.2删除数据：

sess.query(Host).filter(Host.id==1).delete()

4.3修改数据：

sess.query(Host).filter(Host.id==1).update({"port":8888}) 
 # 更新 Host 创建表的类名，filter 过滤

4.4查询数据：

  res = sess.query(Host).filter_by(id=2).all()                 # 查询
    #res = sess.query(Host).filter(Host.id==2).all()
    # filter_by(id==2) 过滤，从Host表
    for r in res:    #r是一个对象
      print(r.hostname)
      print(r.ip_addr)
      print(r.port)
    sess.commit()

输出：

2020-02-27 13:32:21,976 INFO sqlalchemy.engine.base.Engine {'id_1': 2}
test2
192.168.0.1
8000
2020-02-27 13:32:21,977 INFO sqlalchemy.engine.base.Engine COMMIT

4.5完整代码：

#集成ORM类操纵数据库  curd
from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import  declarative_base # 导入我们的基类，就是我们创建的类都是以他为基础
# sessionmaker 用于代替 conn 也就是 engine.connect()
# conn = engine.connect() 获取一个连接

# 连接数据库
engine = create_engine(
  "mysql+pymysql://root:123456@127.0.0.1:3306/test",# （里面的 root 要填写你的密码）,注意：mysql+pymysql 之间不要加空格
  # "mysql + pymysql://root:root@localhost/test",
  max_overflow = 5, # 超过连接池大小之后，外最多可以创建的链接
  pool_size = 10, # 连接池大小
  echo = True, # 调试信息展示
)


Base = declarative_base()
class Host(Base):
  # 表名为 hosts
  __tablename__='hosts'  #建议写 也可以不写
  # 表结构
  # primary_key 等于主键 自动增长
  # unique 唯一 别人和你一样就查不进去 比如你注册时候名字重复不让你用
  # nullable 非空  不容许提交空
  id = Column(Integer,primary_key=True,autoincrement=True)
  hostname = Column(String(64),unique=True,nullable=False)
  ip_addr = Column(String(128),unique=True,nullable=False)
  port = Column(Integer, default=22)  #default 默认填充 就如网站注册你不填给你默认名字
  title = Column(String(200))


#Base.metadata.create_all(engine) # 创建表


if __name__ == '__main__':
  try:
    Session = sessionmaker(bind=engine)  #bind 监听哪一个引擎 这里是engine
    sess = Session() # 创建实例
    '''h = Host(hostname='test1',ip_addr="127.0.0.1")
    h2 = Host(hostname='test2',ip_addr="192.168.0.1",port=8000)
    h3= Host(hostname='test3',ip_addr="192.168.0.3",port=8080)
    sess.add(h) # 每次添加一个
    sess.add_all([h2,h3]) # 每次添加多个
    sess.commit() # 事物提交，必须要有'''
    #sess.query(Host).filter(Host.id==1).update({"port":8888})  # 更新 Host 创建表的类名，filter 过滤
    #sess.query(Host).filter(Host.id==1).delete()               #删除
    #sess.query(Host).filter(Host.id>1).delete()  
   
    res = sess.query(Host).filter_by(id=2).all()                 # 查询
    #res = sess.query(Host).filter(Host.id==2).all()
    # filter_by(id==2) 过滤，从Host表
    for r in res:    #r是一个对象
      print(r.hostname)
      print(r.ip_addr)
      print(r.port)
    sess.commit()

  except:
    print("Error!")
ps：出现编码问题，可以添加 charset=UTF8MB4