python propresql mysql_python操作mysql（pymysql + sqlalchemy）

pymysql

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

下载安装

pip3 install pymysql

使用操作

1、执行sql

#!/usr/bin/env python#-*- coding:utf-8 -*-

importpymysql#创建连接

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', 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、获取新创建数据自增ID

#!/usr/bin/env python#-*- coding:utf-8 -*-

importpymysql

conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', 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

3、获取查询数据

#!/usr/bin/env python#-*- coding:utf-8 -*-

importpymysql

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

cursor=conn.cursor()

cursor.execute("select * from hosts")#获取第一行数据

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') # 相对绝对位置移动

4、fetch数据类型

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

#!/usr/bin/env python#-*- coding:utf-8 -*-

importpymysql

conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')#游标设置为字典类型

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

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

result=cursor.fetchone()

conn.commit()

cursor.close()

conn.close()

一、对象映射关系(ORM)

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言

优点：

隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来

ORM使我们构造固化数据结构变得简单易行

缺点：

无可避免的，自动化意味着映射和关联管理，代价是牺牲性能(早期，这是所有不喜欢ORM人的共同点)。现在的各种ORM框架都在尝试使用各种方法来减轻这块(LazyLoad，Cache)，效果还是很显著的

二、SQLAlchemy

在Python中，最有名的ORM框架是SQLAlchemy。用户包括openstack＼Dropbox等知名公司或应用

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

MySQL-Python

mysql+mysqldb://:@[:]/pymysql

mysql+pymysql://:@/[?]

MySQL-Connector

mysql+mysqlconnector://:@[:]/cx_Oracle

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

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

安装：

pip installSQLAlchemy

pipinstallpymysql

一、内部处理

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

#!/usr/bin/env python#coding=utf-8

from sqlalchemy importcreate_engine

engine= create_engine("mysql+pymysql://root:xiaoming.note5@115.159.193.77:3306/school?charset=utf8", max_overflow=5)#执行SQL

cur =engine.execute("insert into user (name, password) values('lihy', 'lihy')")#新插入行自增ID

cur.lastrowid#执行SQL

cur =engine.execute("insert into user(name, password) values(%s, %s)", [('liq', 'liq'), ('liuxj', 'liuxj235')]

)#执行SQL

cur =engine.execute("insert into user(name, password) values(%(name)s, %(password)s)", name='lium', password='lium123')#执行SQL

cur = engine.execute('select * from user')#获取第一行数据, 第n行，所有数据

cur.fetchone()

cur.fetchmany(3)

cur.fetchall()

二、ORM功能使用

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

1、外键关联

创建表

# orm_fk.py

#!/usr/bin/env python#coding=utf-8

from sqlalchemy importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKey, Datefrom sqlalchemy.orm importrelationship

engine= create_engine("mysql+pymysql://root:xiaoming.note5@115.159.193.77/school", encoding='utf-8')

Base=declarative_base()classStudent(Base):__tablename__ = 'student'id= Column(Integer, primary_key=True)

name= Column(String(32), nullable=False)

age= Column(String(32), nullable=False)

register_date= Column(Date, nullable=False)def __repr__(self):return '' %(self.id, self.name)classStudyRecord(Base):__tablename__ = 'study_record'id= Column(Integer, primary_key=True)

day= Column(Integer,nullable=False)

status= Column(String(32), nullable=False)

stu_id= Column(Integer, ForeignKey('student.id')) #关联student表里的id

student= relationship('Student', backref='my_study_record') # Student为关联的类def __repr__(self):return '' %(self.student.name, self.day, self.status)

Base.metadata.create_all(engine)

注：my_student = relationship("Student",backref="my_study_record")这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项

插入数据

# cat orm_fk

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom orm_fk importStudent, StudyRecord, engine

Session= sessionmaker(bind=engine)

session=Session()

session.add_all([

Student(name='lihy', age=21, register_date='2016-10-15'),

Student(name='liq', age=22, register_date='2016-11-16'),

Student(name='zhuxj', age=23, register_date='2016-12-17'),

StudyRecord(day=1, status='yes', stu_id=1),

StudyRecord(day=2, status='yes', stu_id=1),

StudyRecord(day=3, status='no', stu_id=1),

StudyRecord(day=3, status='yes', stu_id=2),

])

session.commit()

st1 = Student(name='lium', age=22, register_date='2011-10-15')st2 = Student(name='liuxj', age=25, register_date='2011-11-15')

sr1 =StudyRecord(day=4, status='yes', stu_id=1),

sr2 = StudyRecord(day=5, status='yes', stu_id=1),sr3 = StudyRecord(day=6, status='no', stu_id=1),

sr4 = StudyRecord(day=7, status='yes', stu_id=2),

session.add_all([st1,st2,sr1,sr2,sr3,sr4])

session.commit()

查询数据

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom orm_fk importStudent, StudyRecord, engine

Session= sessionmaker(bind=engine)

session=Session()

stu_obj= session.query(Student).filter(Student.name=='lihy').first()print(stu_obj.my_study_record)

2、多外键关联

#!/usr/bin/env python#coding=utf-8

from sqlalchemy importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKey, Datefrom sqlalchemy.orm importrelationship

engine= create_engine("mysql+pymysql://root:xiaoming.note5@115.159.193.77/school", encoding='utf-8')

Base=declarative_base()classCustomer(Base):__tablename__ = 'customer'id= Column(Integer, primary_key=True)

name= Column(String(32))

billing_address_id= Column(Integer, ForeignKey('address.id'))

shipping_address_id= Column(Integer, ForeignKey('address.id'))

billing_address= relationship('Address', foreign_keys=[billing_address_id])

shipping_address= relationship('Address', foreign_keys=[shipping_address_id])def __repr__(self):return '' %(self.name, self.billing_address.street, self.shipping_address.street)classAddress(Base):__tablename__ = 'address'id= Column(Integer, primary_key=True)

street= Column(String(64))

city= Column(String(64))

province= Column(String(64))

Base.metadata.create_all(engine)

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importAddress, Customer, engine

Session= sessionmaker(bind=engine)

session=Session()

session.add_all([

Address(street='huaxia', city='SH', province='ShangHai'),

Address(street='sunhua', city='BJ', province='HeNan'),

Address(street='xihuan', city='XC', province='ShangHai'),

Customer(name='lihy', billing_address_id=1, shipping_address_id=2),

Customer(name='liq', billing_address_id=1, shipping_address_id=1),

])

session.commit()

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importCustomer, Address, engine

Session= sessionmaker(bind=engine)

session=Session()

ret= session.query(Customer).filter(Customer.name=='lihy').first()print(ret.billing_address.street, ret.shipping_address.province)

3、多对多关联

#!/usr/bin/env python#coding=utf-8

from sqlalchemy importcreate_enginefrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importColumn, Integer, String, ForeignKey, Date, Tablefrom sqlalchemy.orm importrelationship

engine= create_engine("mysql+pymysql://root:xiaoming.note5@115.159.193.77/school", encoding='utf-8')

Base=declarative_base()

bookidToAuthorid= Table('bookidToAuthorid', Base.metadata,

Column('bookid', Integer, ForeignKey('books.id')),

Column('authorid', Integer, ForeignKey('authors.id')),

)classBook(Base):__tablename__ = 'books'id= Column(Integer, primary_key=True)

name= Column(String(64))

pub_date=Column(Date)

authors= relationship('Author', secondary=bookidToAuthorid, backref='books')def __repr__(self):returnself.nameclassAuthor(Base):__tablename__ = 'authors'id= Column(Integer, primary_key=True)

name= Column(String(32))def __repr__(self):returnself.name

Base.metadata.create_all(engine)

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importBook, Author, engine

Session= sessionmaker(bind=engine)

session=Session()

b1= Book(name="learn python", pub_date='2011-10-15')

b2= Book(name="learn linux", pub_date='2011-10-16')

b3= Book(name="learn C++", pub_date='2011-10-17')

a1= Author(name="lihy")

a2= Author(name="liq")

a3= Author(name="lium")

b1.authors=[a1, a3]

b3.authors=[a1, a2, a3]

session.add_all([b1, b2, b3, a1, a2, a3])

session.commit()

#!/usr/bin/env python

# coding=utf-8

from sqlalchemy.orm importsessionmaker

from cj importBook, Author, engine

Session = sessionmaker(bind=engine)

session =Session()

ret = session.query(Book).filter(Book.name=='learn python').first()

print(ret.authors)

多对多删除

通过书删除作者

未删前：

[root@VM_255_164_centos mtm]#python3 query.py

[lihy, lium]

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importBook, Author, engine

Session= sessionmaker(bind=engine)

session=Session()

author_obj= session.query(Author).filter(Author.name=='lihy').first()

book_obj= session.query(Book).filter_by(name="learn python").first()

book_obj.authors.remove(author_obj)

session.commit()

# 删除后

#python3 query.py

[lium]

直接删除作者，会把这个作者跟所有书的关联数据也删掉

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importBook, Author, engine

Session= sessionmaker(bind=engine)

session=Session()

author_obj= session.query(Author).filter(Author.name=='lihy').first()

session.delete(author_obj)

session.commit()

查询数据

mysql> select * frombooks;+----+--------------+------------+

| id | name | pub_date |

+----+--------------+------------+

| 1 | learn python | 2011-10-15 |

| 2 | learn C++ | 2011-10-17 |

| 3 | learn linux | 2011-10-16 |

+----+--------------+------------+

3 rows in set (0.00sec)print(session.query(Book.name, Book.pub_date).all())#[('learn python', datetime.date(2011, 10, 15)), ('learn C++', datetime.date(2011, 10, 17)), ('learn linux', datetime.date(2011, 10, 16))]

多条件查询

objs = session.query(Book).filter(Book.id>1).filter(Book.id<3).all()

统计

session.query(Book).filter(Book.name.like('l%')).count()

分组

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importBook, Author, enginefrom sqlalchemy importfunc

Session= sessionmaker(bind=engine)

session=Session()print(session.query(func.count(Book.name), Book.name).group_by(Book.name).all())

# [(1, 'learn C++'), (1, 'learn linux'), (1, 'learn python')]

相当于原声sql：

mysql> select count(books.name) AS count_1, books.name as books_name frombooks group by books.name;+---------+--------------+

| count_1 | books_name |

+---------+--------------+

| 1 | learn C++ |

| 1 | learn linux |

| 1 | learn python |

+---------+--------------+

3 rows in set (0.00 sec)

修改

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importBook, Author, enginefrom sqlalchemy importfunc

Session= sessionmaker(bind=engine)

session=Session()

books_obj= session.query(Book).filter_by(name='learn python').first()print(books_obj.pub_date)

books_obj.pub_date= "2011-11-11"session.commit()print(books_obj.pub_date)#python3 d1.py

2011-10-15

2011-11-11

回滚

#!/usr/bin/env python#coding=utf-8

from sqlalchemy.orm importsessionmakerfrom cj importBook, Author, enginefrom sqlalchemy importfunc

Session= sessionmaker(bind=engine)

session=Session()

books_obj= session.query(Book).filter_by(name='learn python').first()print(books_obj.pub_date)

books_obj.pub_date= "2012-12-12"

print(books_obj.pub_date)

session.rollback()print(books_obj.pub_date)

# 2011-11-11# 2012-12-12# 2011-11-11

其他：

#删

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

session.commit()#增

session.query(Book).filter(Book.id == 2).update({"pub_date": "2013-12-13"})

session.commit()

session.query(Book).filter(Book.id== 2).update({Book.pub_date: Book.pub_date + 10})#查

session.query(Book).all()#条件

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 importand_, 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 importfunc

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

ret= session.query(Person).join(Favor, isouter=True).all()#组合

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