mysql、pymysql、SQLAlchemy

2023-01-01 09:33:00

1.MySQL介绍

http://www.cnblogs.com/wupeiqi/articles/5699254.html，基础操作参见此文章，此处不赘述。

安装：yum install mysql-server

1.1 连表　　　　

　　　　　无对应关系则不显示

        select A.num, A.name, B.name

        from A,B

        Where A.nid = B.nid

        无对应关系则不显示

        select A.num, A.name, B.name

        from A inner join B

        on A.nid = B.nid

        A表所有显示，如果B中无对应关系，则值为null；左边的表为主，左表的所有数据会显示

        select A.num, A.name, B.name

        from A left join B

        on A.nid = B.nid

        B表所有显示，如果B中无对应关系，则值为null；右边的表为主，右表的所有数据会显示

        select A.num, A.name, B.name

        from A right join B

        on A.nid = B.nid

1.2 组合

　　　　组合，自动处理重合，两个表都有的数据只显示一次

　　　　 select nickname from A

　　　　 union

　　　　 select name from B

　　　　组合，不处理重合，两个都有的数据显示两次

　　　　 select nickname from A

　　　　 union all

　　　　 select name from B

2.pymysql

2.1 安装

pip3 install pymysql

2.2 使用

2.2.1 执行SQL

#!/usr/bin/env python

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

import pymysql

# 创建连接

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')

# 创建游标

cursor = conn.cursor()

# 执行SQL，并返回收影响行数

effect_row = cursor.execute("update hosts set host = '1.1.1.2'")

# 执行SQL，并返回受影响行数

#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))

# 执行SQL，并返回受影响行数

#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

# 提交，不然无法保存新建或者修改的数据

conn.commit()

# 关闭游标

cursor.close()

# 关闭连接

conn.close()

2.2.2 获取新创建数据自增ID

如果ID设置的自增，那插入数据后不知道插的ID是多少，可以通过“lastrowid”获取最新插入数据的ID

#!/usr/bin/env python

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

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')

cursor = conn.cursor()

cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

conn.commit()

cursor.close()

conn.close()

# 获取最新自增ID

new_id = cursor.lastrowid

2.2.3 查询数据

#!/usr/bin/env python

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

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')

cursor = conn.cursor()

cursor.execute("select * from hosts")

# 获取第一行数据

row_1 = cursor.fetchone()

#虽然这个也是fetchone，但是会获取第二行；下一个fetchone会获取第三行，这有点类似于yield。

row_1 = cursor.fetchone()

# 获取前n行数据

# row_2 = cursor.fetchmany(3)

# 获取所有数据

# row_3 = cursor.fetchall()

conn.commit()

cursor.close()

conn.close()

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动

2.2.4 fetch数据类型

关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

#!/usr/bin/env python

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

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='t1')

# 游标设置为字典类型

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

r = cursor.execute("call p1()")

result = cursor.fetchone()

conn.commit()

cursor.close()

conn.close()

3. SQLAchemy

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

各个语言都有ORM框架，ORM框架的作用就是将复杂的SQL语句封装起来，让用户可以通过调用对象、类、方法做到操作数据库。

SQLAlchemy本身无法操作数据库，其必须依赖pymsql等第三方插件，Dialect用于和DBAPI进行交流，根据配置文件的不同调用不同的DBAPI，从而实现对数据库的操作，如：

MySQL-Python

    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql

    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector

    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle

    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

安装：pip3 install SQLAlchemy

3.1 底层介绍

使用 Engine/ConnectionPooling/Dialect 这三个组件即可进行数据库操作，Engine（数据库引擎）使用ConnectionPooling（数据库连接池）连接数据库，然后再通过Dialect执行SQL语句。

但仅仅使用Engine/ConnectionPooling/Dialect 这三个组件只是实现了类似pymysql的功能，并没有简化数据库操作，继续往下看。

#!/usr/bin/env python

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

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

# 执行SQL

cur = engine.execute(

     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"

)

#新插入行自增ID

cur.lastrowid

# 执行SQL，可见，使用engine.execute跟使用pymysql一样

cur = engine.execute(

"INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]

)

# 执行SQL

# cur = engine.execute(

#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",

#     host='1.1.1.99', color_id=3

# )

# 执行SQL

# cur = engine.execute('select * from hosts')

# 获取第一行数据

# cur.fetchone()

# 获取第n行数据

# cur.fetchmany(3)

# 获取所有数据

# cur.fetchall()

3.2 ORM功能

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

3.2.1 创建表

#!/usr/bin/env python

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

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

Base = declarative_base() 

# 创建单表


class Users(Base): 
　　__tablename__ = 'users' 
　　id = Column(Integer, primary_key=True) 
　　name = Column(String(32)) 
　　extra = Column(String(16))

# 一对多 
class Favor(Base): 
　　__tablename__ = 'favor' 
　　nid = Column(Integer, primary_key=True) 
　　caption = Column(String(50), default='red', unique=True) 

class Person(Base):
　　__tablename__ = 'person' 
　　nid = Column(Integer, primary_key=True) 
　　name = Column(String(32), index=True, nullable=True) 
　　favor_id = Column(Integer, ForeignKey("favor.nid")) 

# 多对多 
class Group(Base): 
　　__tablename__ = 'group' 
　　id = Column(Integer, primary_key=True) 
　　name = Column(String(64), unique=True, nullable=False) 
　　port = Column(Integer, default=22) 

class Server(Base): 
　　__tablename__ = 'server' 
　　id = Column(Integer, primary_key=True, autoincrement=True) 
　　hostname = Column(String(64), unique=True, nullable=False) 

class ServerToGroup(Base):
　　__tablename__ = 'servertogroup' 
　　nid = Column(Integer, primary_key=True, autoincrement=True) 
　　server_id = Column(Integer, ForeignKey('server.id')) 
　　group_id = Column(Integer, ForeignKey('group.id')) 

def init_db(): 
　　Base.metadata.create_all(engine) 

def drop_db(): 
　　Base.metadata.drop_all(engine)

3.2.2 操作表

Session = sessionmaker(bind=engine)

session = Session()

obj = Users(name="alex0", extra='sb')

session.add(obj)

session.add_all([

    Users(name="alex1", extra='sb'),

    Users(name="alex2", extra='sb'),

])

session.commit()

删

session.query(Users).filter(Users.id > 2).delete()

session.commit()

改

session.query(Users).filter(Users.id > 2).update({"name" : ""})

session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + ""}, synchronize_session=False)

session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate")

session.commit()

查

ret = session.query(Users).all()

ret = session.query(Users.name, Users.extra).all()

ret = session.query(Users).filter_by(name='alex').all()

ret = session.query(Users).filter_by(name='alex').first()

其他

#　条件

ret = session.query(Users).filter_by(name='alex').all()

ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()

ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()

ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

from sqlalchemy import and_, or_

ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()

ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()

ret = session.query(Users).filter(

    or_(

        Users.id < 2,

        and_(Users.name == 'eric', Users.id > 3),

        Users.extra != ""

    )).all()

# 通配符

ret = session.query(Users).filter(Users.name.like('e%')).all()

ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制

ret = session.query(Users)[1:2]

# 排序

ret = session.query(Users).order_by(Users.name.desc()).all()

ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组

from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()

ret = session.query(

    func.max(Users.id),

    func.sum(Users.id),

    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(

    func.max(Users.id),

    func.sum(Users.id),

    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()  //这是inner join

ret = session.query(Person).join(Favor, isouter=True).all()   //因为有isouter=True，这就变成了left join；没有right join，但可以改变两个表的位置。

# 组合

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union_all(q2).all()

码农公寓

1.MySQL介绍

1.1 连表

1.2 组合

2.pymysql

2.1 安装

2.2 使用

2.2.1 执行SQL

2.2.2 获取新创建数据自增ID

2.2.3 查询数据

2.2.4 fetch数据类型

3. SQLAchemy

3.1 底层介绍

3.2 ORM功能

3.2.1 创建表

3.2.2 操作表

相关文章

1.1 连表　　　　