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='', 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='', 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 -*-

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

# 获取前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='', 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 SQLAlchemy

pip install pymysql

一、内部处理

使用 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: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 import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, Date

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://root:xiaoming.note5@115.159.193.77/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-8

from sqlalchemy.orm import sessionmaker

from orm_fk import Student, 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 import sessionmaker

from orm_fk import Student, 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 import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, Date

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://root:xiaoming.note5@115.159.193.77/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-8

from sqlalchemy.orm import sessionmaker

from 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-8

from sqlalchemy.orm import sessionmaker

from cj import Customer, 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 import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, Date, Table

from sqlalchemy.orm import relationship

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')),

    )

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.name

class Author(Base):

    __tablename__ =  'authors'

    id = Column(Integer, primary_key=True)

    name = Column(String(32))

    def __repr__(self):

        return self.name

Base.metadata.create_all(engine)
#!/usr/bin/env python

# coding=utf-8

from sqlalchemy.orm import sessionmaker

from cj import Book, 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 import sessionmaker

from cj import Book, 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 import sessionmaker

from cj import Book, 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 import sessionmaker

from cj import Book, 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 * 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-8

from sqlalchemy.orm import sessionmaker

from cj import Book, Author, engine

from sqlalchemy import func

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 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-8

from sqlalchemy.orm import sessionmaker

from cj import Book, Author, engine

from sqlalchemy import func

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 import sessionmaker

from cj import Book, Author, engine

from sqlalchemy import func

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

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

最新文章

  1. poj1157LITTLE SHOP OF FLOWERS
  2. spring+hibernate－－直接修改数据库，再通过hibernate查询数据不变
  3. VS Bug 当获取其他项目的代码时, F5 无法进入调试模式. 也不报错....
  4. PHP SESSION 保存到数据库
  5. 李洪强iOS开发之 - 实现九宫格并使用SDWebImage下载图片
  6. IIS上的错误与解决方案
  7. 点击itemView选中checkbox
  8. 【Cocos2d-x】源代码分析之 2d/ui/Widget
  9. add jars和add external jars有什么区别
  10. Android性能测试——Allocation Tracker(Device Monitor)
  11. SSH（Spring_SpringMVC_Hibernate）
  12. C语言实型常量
  13. mysql----------mysql的一些常用命令
  14. 分布式系统监视zabbix讲解六之自定义监控项--技术流ken
  15. Java面试题归类
  16. 可能比文档还详细--VueRouter完全指北
  17. 《linux内核设计与实现》第四章
  18. Unity 3D还原Scene场景、市面多数游戏视角高度自定义、第三人称视角分离功能：平移、拖动、看向中心等
  19. Dependency Parsing -13 chapter（Speech and Language Processing）
  20. 关于Unity中网格导航与寻路

热门文章

  1. BZOJ2213 [Poi2011]Difference 【乱搞】
  2. 洛谷 P3942 将军令 解题报告
  3. 洛谷 P1053 逛公园 解题报告
  4. 针对Weblogic测试的一些小总结(转）
  5. 利用signapk.jar工具对apk文件进行签名
  6. 从function的定义看JavaScript的预加载
  7. imuxsock lost 353 messages from pid 20261 due to rate-limiting 解决办法
  8. STM32 ------ HardFault_Hander 中断函数
  9. python基础2--字典
  10. vue的props和$attrs