Python操作数据库

python使用MySQL主要有两个模块，pymysql（MySQLdb）和SQLAchemy。
　　pymysql（MySQLdb）为原生模块，直接执行sql语句，其中pymysql模块支持python 2和python3，MySQLdb只支持python2，两者使用起来几乎一样。
　　SQLAchemy为一个ORM框架，将数据对象转换成SQL，然后使用数据API执行SQL并获取执行结果
　　另外DBUtils模块提供了一个数据库连接池，方便多线程场景中python操作数据库。

python中常用组件redis，rabbitmq，mongodb，ES（lasticsearch），zk。

pip install pymysql pymongo redis

pymysql学习

#关于中文问题
#1. mysql命令行创建数据库，设置编码为gbk：create databse demo2 character set utf8; 
#2. python代码中连接时设置charset="gbk"
#3. 创建表格时设置default charset=utf8
 
#连接数据库
conn = pymysql.connect(host="localhost", user="root", passwd="", db='learningsql', charset='utf8', port=3306)  #和mysql服务端设置格式一样（还可设置为gbk, gb2312）
#创建游标
cursor = conn.cursor()
#执行sql语句
cursor.execute("""create table if not exists t_sales(
                id int primary key auto_increment not null,
                 nickName varchar(128) not null,
                 color varchar(128) not null,
                  size varchar(128) not null, 
                  comment text not null,
                  saledate varchar(128) not null)engine=InnoDB default charset=utf8;""") #提交
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()

 

SQLAlchemy学习

SQLAlchemy的整体架构如下，建立在第三方的DB API上，将类和对象操作转换为数据库sql，然后利用DB API执sql语句得到结果。其适用于多种数据库。另外其内部实现了数据库连接池，方便进行多线程操作。

SQLAlchemy是Python SQL工具包和对象关系映射器，是python中最著名的ORM(Object Relationship Mapping)框架，它简化了应用程序开发人员在原生SQL上的操作，使开发人员将主要精力都放在程序逻辑上，从而提高开发效率。它提供了一整套著名的企业级持久性模式，设计用于高效和高性能的数据库访问。

使用ORM操作数据库：
　　优势 ：代码易读，隐藏底层原生SQL语句，提高了开发效率。
　　劣势 ：执行效率低 ，将方法转换为原生SQL后 原生SQL不一定是最优的。

pip install sqlalchemy

1、创建数据库实例

sqlalchemy.create_engine(*args, **kwargs)：创建数据库实例。　

常用参数：

　　Engine：用URL的方式填写连接数据库所需的数据。如下格式：

db = create_engine('mysql+pymysql://数据库用户:数据库密码@127.0.0.1:3306/数据库名?charset=utf8') 

　　case_sensitive=True：如果为False，在查询获取列名时将不区分大小写。

　　connect_args：值是一个字典，用于设置数据库连接参数，连接数据库时可以传递自定义参数。如下将连接信息存放到字典中。

# 将连接信息放到connect_args字典中
db = create_engine('mysql+pymysql://', connect_args={'user': '用户名',
                                                     'password': '密码',
                                                     'host': '数据库地址',
                                                     'port': 3306,
                                                     'database': '数据库',
                                                     'charset': 'utf8'})

　　creator：值是函数名，该函数返回数据库连接对象。如下：

from sqlalchemy import create_engine
import pymysql
# 使用pymysql获取连接对象
def connect_sql():
    connect = pymysql.connect(
        host='数据库地址',
        user='用户名',
        password='密码',
        db='数据库',
        charset='utf8',
    )
    return connect
# 获取数据库实例
db = create_engine('mysql+pymysql://',creator=connect_sql)

　　echo=False：如果为True会将执行的SQL语句及引擎执行的过程写到默认的日志当中，默认日志使用sys.stdout作为输出。echo的值除了True|False还可以设置为'debug'等字符串的形式来调整日志等级。如下：

# 设置日志等级
db = create_engine('mysql+pymysql://', connect_args={'user': '用户名',
                                                     'password': '密码',
                                                     'host': '数据库地址',
                                                     'port': 3306,
                                                     'database': '数据库',
                                                     'charset': 'utf8'},echo='debug')

　　echo_pool=False：如果为True将会记录连接池的迁入与迁出，默认日志使用sys.stdout作为输出，echo_pool的值除了True|False还可以设置为'debug'等字符串的形式来调整日志等级。

　　encoding：SQLAlchemy对从数据库中获取的数据编码|解码方式，默认是utf-8。

　　isolation_level：数据库事物隔离，值是："SERIALIZABLE"、 "REPEATABLE_READ"、 "READ_COMMITTED"、"READ_UNCOMMITTED" 、"AUTOCOMMIT".，具体以后端使用的数据库为准。

　　logging_name：sqlalchemy.engine记录器中生成的日志记录的“name”字段，默认为对象id的十六进制字符串。

　　max_identifier_length：值是一个整数，用于设置SQL标识符（如表名、列名或标签名）的最大字符数。

　　max_overflow=10：允许连接池内“溢出”的连接数，即在池大小设置之外打开的连接数。这只用于QueuePool。

　　module=None ：对Python模块对象（模块本身，而不是其字符串名称）的引用，如mysql使用pymysql驱动，此参数将绕过导入，而使用给定的模块，直接操作数据库。可用于测试DBAPI以及将“模拟”DBAPI实现注入引擎。

　　pool=None：如果值不是None，则是pool的实例或者QueuePool 的实例，此时将绕过URL参数中的连接参数，使用给定的pool或QueuePool作为engine的基础连接池。所谓pool池其实就是指数据库连接实例。 

　　pool_size=5：连接池中保持打开状态的连接数，池大小设置为0表示没有限制，禁用池，将poolclass设置为NullPool。

　　poolclass=None：用户自定义连接池。

　　pool_recycle=-1 ：在给定的秒数之后回收连接。默认为-1表示没有超时。例如，设置为3600意味着连接将在一小时内连接上如果没有任何活动将被回收。注意，如果在八小时内未在连接上检测到任何活动，MySQL将自动断开连接（尽管这可以通过MySQLDB连接本身和服务器配置进行配置）。

　　pool_reset_on_return='rollback'：设置底层池对象的Pool.reset_on_return参数，该参数可以设置为值“rollback”、“commit”或“None”。

　　pool_timeout=30：获取连接池的超时时间单位是秒。

　　pool_use_lifo=False：从队列池检索连接时使用后进先出，而不是先进先出。使用后进先出，服务器端超时方案可以减少在非高峰使用期间使用的连接数。在计划服务器端超时时，请确保使用了回收或预ping策略来优雅地处理过时的连接。

　　plugins：要加载的插件名称的字符串列表。

2、连接数据库，获取数据库实例。

SQLAlchmy也可以不利用ORM，使用数据库连接池，类似pymysql模块执行原生sql

from sqlalchemy import create_engine
import pymysql
# 第一种获取数据库实例的方法
def connect_sql():
    # 使用pymysql获取连接对象
    connect = pymysql.connect(
        host='数据库地址',
        user='用户名',
        password='密码',
        db='数据库',
        charset='utf8',
    )
    return connect
# 获取数据库实例
db = create_engine('mysql+pymysql://',creator=connect_sql)


# 第二种获取数据库实例的方法
db = create_engine('mysql+pymysql://', connect_args={'user': '用户名',
                                                     'password': '密码',
                                                     'host': '数据库地址',
                                                     'port': 3306,
                                                     'database': '数据库',
                                                     'charset': 'utf8'})

# 第三种获取数据库实例的方法
db = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')

cur = engine.execute("show tables")
result = cur.fetchone()
cur.close()
print(result)

 

3、在数据库中创建和删除表

在创建表时由于是使用的ORM，所以需要将创建好的类与SQLAlchemy底层建表的类进行映射，类似于Django中类要继承model类一样。

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, DateTime, Text

# 创建对象的基类:
Base = declarative_base()
# 定义User对象:
class User(Base):
    # 设置表的名字:
    __tablename__="users"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),index=True, nullable=False) #创建索引，不为空
    email = Column(String(32),unique=True)
    ctime = Column(DateTime, default = datetime.datetime.now)  #传入方法名datetime.datetime.now
    extra = Column(Text,nullable=True)  
    
    __table_args__ = {
    
        # UniqueConstraint('id', 'name', name='uix_id_name'), #设置联合唯一约束
        # Index('ix_id_name', 'name', 'email'),               # 创建索引
    }
 
def create_tbs():
    # 获取数据库实例
    engine = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')
    Base.metadata.create_all(engine)   #创建所有定义的表
 
def drop_dbs():
    # 获取数据库实例
    engine = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')
    Base.metadata.drop_all(engine)   #删除所有创建的表
 
if __name__=="__main__":
    create_tbs() #创建表
    #drop_dbs()   #删除表

 4、向表中添加数据

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


# 建立映射关系
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 设置表明

    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

# 向表中添加记录
obj = User(name='小明',age=12)

"""
# 插入多条记录
obj = [User(name='小丽', age=12),
       User(name='李红', age=14),
       User(name='小刚', age=10)]
"""


# 获取数据库实例
db = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')

# 创建会话
obj_session = sessionmaker(db)
# 打开会话
db_session = obj_session()
# 向表中添加数据,此时数据保存在内存中
db_session.add(obj)
# 提交数据，将数据保存到数据库中
db_session.commit()
# 关闭会话
db_session.close()

5、查询表中数据

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 建立映射关系
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 设置表明

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

# 获取数据库实例
db = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')

# 创建会话
obj_session = sessionmaker(db)
# 打开会话
db_session = obj_session()

# 查询表中所有数据
all_list = db_session.query(User).all()
for obj in all_list:
    print(obj.id,obj.name,obj.age)
    
# 关闭会话
db_session.close()

 6、使用filter过滤查询条件

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 建立映射关系
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 设置表明

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

# 获取数据库实例
db = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')

# 创建会话
obj_session = sessionmaker(db)
# 打开会话
db_session = obj_session()

# 使用filter过滤查询条件
all_list = db_session.query(User).filter(User.name=='小明')
for obj in all_list:
    print(obj.id,obj.name,obj.age)
# 查看SQLAlchemy底层执行的sql语句
print(f'执行的sql语句：{all_list}')

# 关闭会话
db_session.close()

7、 修改数据

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 建立映射关系
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 设置表明

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

# 获取数据库实例
db = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')

# 创建会话
obj_session = sessionmaker(db)
# 打开会话
db_session = obj_session()
# 将小明的名字修改为娃哈哈
all_list = db_session.query(User).filter(User.name=='小明').update({'name':'娃哈哈'})
# 提交数据，将数据保存到数据库中
db_session.commit()
# 查看更改后的表
all_data = db_session.query(User).all()
for obj in all_data:
    print(obj.id,obj.name,obj.age)
# 关闭会话
db_session.close()

8、删除数据

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


# 建立映射关系
Base = declarative_base()
class User(Base):
    __tablename__ = 'users'  # 设置表明

    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    age = Column(Integer)

# 获取数据库实例
db = create_engine('mysql+pymysql://数据库用户:数据库密码@数据库地址:3306/数据库名?charset=utf8')

# 创建会话
obj_session = sessionmaker(db)
# 打开会话
db_session = obj_session()
# 删除id是1的行
all_list = db_session.query(User).filter(User.id==1).delete()
# 提交数据，将数据保存到数据库中
db_session.commit()
# 删除后查看表中数据
all_data = db_session.query(User).all()
for obj in all_data:
    print(obj.id,obj.name,obj.age)
# 关闭会话
db_session.close()

9、回滚

在 commit() 之前，对实例对象的属性所做的更改，可以进行回滚，回到更改之前。session.rollback()

10、查询常用筛选器运算符

# 等于
query.filter(User.name == 'ed')

# 不等于
query.filter(User.name != 'ed')

# like和ilike
query.filter(User.name.like('%ed%'))
query.filter(User.name.ilike('%ed%')) # 不区分大小写

# in
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
query.filter(User.name.in_(
    session.query(User.name).filter(User.name.like('%ed%'))
))
# not in
query.filter(~User.name.in_(['ed', 'wendy', 'jack'])) 

# is
query.filter(User.name == None)
query.filter(User.name.is_(None))

# is not
query.filter(User.name != None)
query.filter(User.name.is_not(None))

# and
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

# or
from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))

# match
query.filter(User.name.match('wendy'))