python操作mysql（pymysql + sqlalchemy），pymysqlsqlalchemy,pymysqlpym

python操作mysql（pymysql + sqlalchemy），pymysqlsqlalchemy,pymysqlpym

pymysql

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

下载安装

pip3 install pymysql

使用操作

1、执行sql

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql  # 创建连接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 -*-import pymysql  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()  # 获取最新自增IDnew_id = cursor.lastrowid

3、获取查询数据

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql  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 -*-import pymysql  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://<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

安装：

pip install SQLAlchemypip install pymysql 

一、内部处理

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

#!/usr/bin/env python# coding=utf-8from sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:[email protected]:3306/school?charset=utf8", max_overflow=5)# 执行SQLcur = engine.execute(    "insert into user (name, password) values(‘lihy‘, ‘lihy‘)"    )# 新插入行自增IDcur.lastrowid# 执行SQLcur = engine.execute(    "insert into user(name, password) values(%s, %s)", [(‘liq‘, ‘liq‘), (‘liuxj‘, ‘liuxj235‘)]    )# 执行SQLcur = engine.execute(    "insert into user(name, password) values(%(name)s, %(password)s)", name=‘lium‘, password=‘lium123‘    )# 执行SQLcur = 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-8from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, Datefrom sqlalchemy.orm import relationshipengine = create_engine("mysql+pymysql://root:[email protected]/school", encoding=‘utf-8‘)Base = declarative_base()class Student(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 ‘<%s name:%s>‘ % (self.id, self.name)class StudyRecord(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 ‘<%s day:%s status:%s>‘ % (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-8from sqlalchemy.orm import sessionmakerfrom orm_fk import Student, StudyRecord, engineSession = 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-8from sqlalchemy.orm import sessionmakerfrom orm_fk import Student, StudyRecord, engineSession = 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-8from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, Datefrom sqlalchemy.orm import relationshipengine = create_engine("mysql+pymysql://root:[email protected]/school", encoding=‘utf-8‘)Base = declarative_base()class Customer(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 ‘<%s name:%s billing_address:%s shipping_adress>‘ % (self.name, self.billing_address.street, self.shipping_address.street)class Address(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-8from sqlalchemy.orm import sessionmakerfrom cj import Address, 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-8from sqlalchemy.orm import sessionmakerfrom cj import Customer, Address, engineSession = 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-8from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, Date, Tablefrom sqlalchemy.orm import relationshipengine = create_engine("mysql+pymysql://root:[email protected]/school", encoding=‘utf-8‘)Base = declarative_base()bookidToAuthorid = Table(‘bookidToAuthorid‘, Base.metadata,        Column(‘bookid‘, Integer, ForeignKey(‘books.id‘)),        Column(‘authorid‘, Integer, ForeignKey(‘authors.id‘)),    )class Book(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):        return self.nameclass Author(Base):    __tablename__ =  ‘authors‘    id = Column(Integer, primary_key=True)    name = Column(String(32))    def __repr__(self):        return self.nameBase.metadata.create_all(engine)

#!/usr/bin/env python# coding=utf-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, engineSession = 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-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, engineSession = sessionmaker(bind=engine)session = Session()ret = session.query(Book).filter(Book.name==‘learn python‘).first()print(ret.authors)

多对多删除

　　通过书删除作者

未删前：
[[email protected]_255_164_centos mtm]# python3 query.py[lihy, lium]

#!/usr/bin/env python# coding=utf-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, engineSession = 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-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, engineSession = sessionmaker(bind=engine)session = Session()author_obj = session.query(Author).filter(Author.name==‘lihy‘).first()session.delete(author_obj)session.commit()

查询数据

mysql> select * from books;+----+--------------+------------+| 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.00 sec)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-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, enginefrom sqlalchemy import funcSession = 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 from books 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-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, enginefrom sqlalchemy import funcSession = 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-152011-11-11

回滚

#!/usr/bin/env python# coding=utf-8from sqlalchemy.orm import sessionmakerfrom cj import Book, Author, enginefrom sqlalchemy import funcSession = 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 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 funcret = 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()

python操作mysql（pymysql + sqlalchemy）