Python操作MySQL、SQLAchemy

本篇对于Python操作MySQL主要使用两种方式：

原生模块 pymsql
ORM框架 SQLAchemy

参考资料：http://www.runoob.com/python/python-mysql.html

一、pymsql

pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

下载安装

pip3 install pymysql -i https://pypi.douban.com/simple

使用操作

1、执行SQL

写sql语句的时候，如果在操作前sql语句字符串格式化，占位符%s要引号' '引起来，如果执行操作，以元组或者列表的形式，在提交的时候传递参数，则占位符%s就不能引号引起来。

import pymysql

user = input("username:")
pwd = input("password:")

# 创建连接
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
# 出现编码问题conn = pymysql.connect(host="localhost",user='root',password='',database="db666",charset="utf8")

# 创建游标
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,)

# select * from userinfo where username='uu' or 1=1 -- ' and password='%s' #sql注入

# 执行SQL，并返回收影响行数
cursor.execute(sql)
# 获取第一行数据
result = cursor.fetchone()
cursor.close()
conn.close()

if result:
    print('登录成功')
else:
    print('登录失败')

注意：存在中文的时候，连接需要添加charset='utf8'，否则中文显示乱码。

获取查询数据

结论：excute执行SQL语句的时候，必须使用参数化的方式，否则必然产生SQL注入漏洞。

import pymysql

user = input("username:")
pwd = input("password:")

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "select * from userinfo where username=%s and password=%s"
cursor.execute(sql,(user,pwd))  #推荐这种方法，防止sql注入 
# cursor.execute(sql,[user,pwd])
# cursor.execute(sql,{'u':user,'p':pwd})
#查询一行
result = cursor.fetchone()
#查询全部
result = cursor.fetchall()
#查询四行
result = cursor.fetchmany(4)
cursor.close()
conn.close()
if result:
    print('登录成功')
else:
    print('登录失败')

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

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

插入多行数据

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values(%s,%s)"
# 插入多行数据
r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])
#  ******
# 提交，不然无法保存新建或者修改的数据
conn.commit()
cursor.close()
conn.close()

报错信息：
Error: unable to insertdb!
insert into class(title) values(%s)
not all arguments converted during string formatting

sql = "insert into class(title) values(%s)"
li = [('1期',), ('2期',)]  #  只插入一列时这么写可以解决，sql语句也不能%s也不能‘’引起来
cursor.executemany(sql,li)

#sql = "insert into userinfo(name,email,gender_id) values(%s,%s,%s)" 
#db.insertmanydb(sql, [('egon', 'sb',1), ('laoyao', 'BS',1)])

fetch数据类型

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

# 查
conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
# 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo"
cursor.execute(sql)

cursor.scroll(1,mode='relative')  # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
# 查询一行
result = cursor.fetchone()
print(result)
# 查询全部
result = cursor.fetchall()
print(result)
# 查询4行
result = cursor.fetchmany(4)
print(result)
cursor.close()
conn.close()

插入单条数据，获取新创建数据自增ID

# 新插入数据的自增ID: cursor.lastrowid
import pymysql

conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
cursor.execute(sql)
conn.commit()
# 新插入数据的自增ID，插入多条时也是拿到最后一条的ID
print(cursor.lastrowid)
cursor.close()
conn.close()

sql语句插入中内容同时包含单引号和双引号的解决办法

在python中调用MySQLdb模块插入数据信息，假设待输入信息data为：

Hello'World"!

其中同时包含了单引号和双引号

一般插入语句为

sql = "insert into tb (my_str) values('%s')" % (data)
cursor.execute(sql)

其中values('%s')中的%s外面也要有引号，这个引号与data中的引号匹配导致了内容错误

解决办法一: MySQLdb.escape_string()

在MySQLdb模块中自带针对mysql的转义函数escape_string()，直接调用即可

sql = "insert into tb (my_str) values('%s')" % (MySQLdb.escape_string(data))
cursor.execute(sql)

解决办法二：转义字符

将data变为下面的形式,再插入数据库就正确了

Hello\'World\"!

具体在python中的转义函数如下：

def transferContent(self, content):
        if content is None:
            return None
        else:
            string = ""
            for c in content:
                if c == '"':
                    string += '\\\"'
                elif c == "'":
                    string += "\\\'"
                elif c == "\\":
                    string += "\\\\"
                else:
                    string += c
            return string

要加三个\，这是因为\\会在函数中转义为\，\'会转义成'，两者合起来才能在字符串中留下 \'，然后sql读取后才能识别这是转义

注意，\本身也需要转义，否则如果原本字符串中为\',只转义'就会变成\\\\',结果是\\\\相互抵消，只剩下'

在python中，下面两种写法是一样的

a=" ' "
a=" \' "

二、SQLAchemy

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

Object Relational Mapper (ORM) 关系对象映射（就是自己写的类）

Schema/Type 、SQL Expression Lanuage将类转换成SQL语句

DBAPI就是(pymysql、mysqlDB、SQL、oracle等中间件接口)，通过这些东西连接不同的数据库

Engine（引擎启动），然后去connection pooling连接池看建立几个连接，然后去Dialect拿配置（如mysql+pymysql），通过中间件接口连接对应数据库

安装：

pip3 install SQLAlchemy

面向对象：

class Foo:
    def __init__(self,name):
        self.name = name
        
    
    def show(self):
        print(self.name)
        
    def __call__(self):
        pass
        
    def __getitem__(self,key):
        pass
        
    def __setitem__(self,key,value):
        pass
        
    def __delitem__(self,key):
        pass
        
obj1 = Foo('eric')


obj1()            执行__call__方法
obj1['k']        执行__getitem__方法
obj1['k'] = 123    执行__setitem__方法
del obj[k]        执行__delitem__方法
obj.__dict__对象的全部属性

特殊方法：

　1. 提取共性
   2. 分类
   3. 模板“约束”
   　　面向对象：数据和逻辑（属性和行为）组合在一起
   　　函数编程：数据和逻辑分离
   4. 当一类函数公用同样参数时候，可以转变成类进行 - 分类

类：对应表

对象：对应行

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

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

一、内部处理

使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

#!/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

# 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()

View Code

二、ORM功能使用

对象-关系映射（OBJECT/RELATIONALMAPPING，简称ORM），是随着面向对象的软件开发方法发展而产生的。用来把对象模型表示的对象映射到基于S Q L 的关系模型数据库结构中去。这样，我们在具体的操作实体对象的时候，就不需要再去和复杂的 SQ L 语句打交道，只需简单的操作实体对象的属性和方法。O R M 技术是在对象和关系之间提供了一条桥梁，前台的对象型数据和数据库中的关系型的数据通过这个桥梁来相互转化。

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

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 UserType(Base):
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=True, index=True)


class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=True, index=True)
    email = Column(String(16), unique=True)
    user_type_id = Column(Integer,ForeignKey("usertype.id"))

    user_type = relationship("UserType",backref="xxoo")
    #前面的给Users使用  后面的给UserType使用
    # relationship：和外键绑定，在一起使用，根据外键连表
    # 正向操作   有外键.relationship是正向操作
    # 反向操作
Base.metadata.create_all(engine) #找到继承Base的类，写到数据库，创建表
Base.metadata.drop_all(engine)
# def init_db():
#     Base.metadata.create_all(engine)

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

#
# init_db()

2、操作表

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




Base = declarative_base()

# 创建单表
class UserType(Base):
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=True, index=True)


class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=True, index=True)
    email = Column(String(16), unique=True)
    user_type_id = Column(Integer,ForeignKey("usertype.id"))

    user_type = relationship("UserType",backref="xxoo")
  #前面的给Users使用  后面的给UserType使用
    # relationship：和外键绑定，在一起使用，根据外键连表
    # 正向操作   有外键.relationship是正向操作
    # 反向操作

engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/db4?charset=utf8", max_overflow=5)
# max_overflow=5  连接数据库的最大连接数
Session = sessionmaker(bind=engine)
session = Session()     #在链接池拿一个链接

# 类----》表

# 对象---》行

# 添加单个

obj = UserType(title = '普通用户') session.add(obj)

#添加多个数据

objs = [UserType(title = '超级用户'), UserType(title = '白金用户'), UserType(title = '黑金用户'), ] session.add_all(objs)

# 查询

print(session.query(UserType))

#sql语句

user_type_list=session.query(UserType).all() print(type(user_type_list))

#是一个列表

print((user_type_list)[0])

#是类一个对象，对象代表行

for row in user_type_list:

　　print(row.id,row.title)

#相当于where条件语句,query(UserType.id,UserType.name)确定取哪一列

user_type_list=session.query(UserType.id,UserType.title).filter(UserType.id>2)

for row in user_type_list:

　　print(row.id,row.title)

#删除，先查再删除

session.query(UserType.id,UserType.title).filter(UserType.id>2).delete()

# 修改，先查再修改

session.query(UserType.id,UserType.title).filter(UserType.id>0).update({"title","黑金"}) #整列都改变

session.query(UserType.id,UserType.title).filter(UserType.id>0).update({UserType.title:UserType.title+"x"},synchronize_session=False) #改变单独的每行，字符串拼接

session.query(Users).filter(Users.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate") #改变单独的每行，计算

# 1.select * from b where id in (select id from tb2)

# 2.select * from (select * from tb) as B

q1 = session.query(UserType).filter(UserType.id>0).subquery() #子查询要加subquery()

result = session.query(q1).all()

print(result)

# select id ,(select id from wherer id = x) from xxx;

result = session.query(UserType.id,session.query(Users).subquery()).all()

# 3.select id ,(slect * from Users) from UserType

result = session.query(UserType.id,session.query(Users).filter(Users.id==UserType.id).as_scalar())

#as_scalar()，将表变成一个元素放置，避免笛卡尔机

print(result)

# 问题一：获取用户信息以及与其关联的用户类型名称一对一

# 连表查询

use_list = session.query(Users,UserType).join(UserType, isouter=True).all() #结果是类元组

use_list = session.query(Users.name,UserType.title).join(UserType, isouter=True).all() #结果是行结果元组

for row in use_list: print(row[0],row[1],row.name,row.title)

use_list = session.query(Users)

for row in use_list:

　　print(row.name,row.id,row.user_type.title) #relation拿到相关联的表的某行正向操作

# 问题二：获取用户类型名称对应的用户一对多

use_list = session.query(UserType) for row in use_list:

print(row.id,row.title,session.query(Users).filter(Users.user_type_id == row.id).all())

use_list = session.query(UserType)