Python大神-python操作Mysql

Python操作Mysql的方式有两种：

　　1）python的第三种模块：pymysql

　　2）ORM框架

一、pymysql

　　1、下载安装

　　　　pip3 install pymysql

　　2、操作方法

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 # 创建连接
 6 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 7 # 创建游标
 8 cursor = conn.cursor()
 9 
10 ========sql语句的操作区间（都是把sql语句以字符串的形式来写）=====================
11   
12 # 执行SQL，并返回收影响行数
13 effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
14   
15 # 执行SQL，并返回受影响行数
16 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))  # 不可用Python中的字符串替换，否则会有漏洞
17   
18 # 执行SQL，并返回受影响行数
19 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
20   
21 ========sql语句的操作区间=====================
22   
23 # 提交，不然无法保存新建或者修改的数据
24 conn.commit()
25   
26 # 关闭游标
27 cursor.close()
28 # 关闭连接
29 conn.close()

　　3、获取新创建数据自增ID（数据库表的最新id值）

1 # 获取最新自增ID
2 new_id = cursor.lastrowid

　　4、获取查询数据--fetchone;fetchmany();fetchall

　　　　注：1）获取查询数据的结果为元组类型

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

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

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6 cursor = conn.cursor()
 7 cursor.execute("select * from hosts")
 8   
 9 # 获取第一行数据
10 row_1 = cursor.fetchone()
11   
12 # 获取前n行数据
13 # row_2 = cursor.fetchmany(3)
14 # 获取所有数据
15 # row_3 = cursor.fetchall()
16   
17 conn.commit()
18 cursor.close()
19 conn.close()

查看获取处理的数据

　　5、修改fetch数据类型

1 # 游标设置为字典类型
2 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

二、ORM（sqlAlchemy）

　　1、准备工作

　　　　ORM是无法独立对数据库进行操作，需要与数据库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

　　2、安装

　　　　1)pip3 install pymysql # 数据库API

　　　　2) pip3 install SQLAlchemy

　　3、简单操作数据表（onlyone）

 1 import pymysql
 2 from sqlalchemy import create_engine # 确定数据库类型、数据库与框架的接口（pymysql）、连接数据库
 3 from sqlalchemy.ext.declarative import declarative_base # 创建基类
 4 from sqlalchemy import Column,Integer,String,PrimaryKeyConstraint,ForeignKey,UniqueConstraint,Index # 创建表所用的关键字
 5 from sqlalchemy.orm import sessionmaker
 6 
 7 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/day3")
 8 
 9 # 步骤1：创建表的基类
10 Base = declarative_base()
11 
12 # 步骤2：创建表的类
13 class User(Base):
14     __tablename__ = "user"
15 
16     id = Column(Integer,primary_key=True)
17     name = Column(String(32))
18     age = Column(Integer)
19     aihao = Column(String(32))
20 
21     __table_args__ = (
22         # PrimaryKeyConstraint(id),
23         UniqueConstraint('name',name='uq_name'),
24     )
25 
26 # 步骤4：新建和删除表
27 # Base.metadata.drop_all(engine)
28 Base.metadata.create_all(engine)
29 
30 # 步骤5：连接资源池-操作表
31 Session = sessionmaker(bind=engine)
32 session = Session()
33 
34 # 两种增加数据的方式
35 """
36 u1 = User(name='sdy',age='23',aihao='paobu')
37 session.add(u1)
38 session.add_all(
39     [User(name='one',age='22',aihao='ss'),
40     User(name='two',age='12',aihao='dd'),]
41 )
42 """
43 
44 # 删除-- delete()
45 # session.query(User).filter(User.id == 2).delete()
46 
47 # 改-- update()
48 # session.query(User).filter_by(name='one').update({"name":"sb"})
49 # 注：1）filter 与 filter_by 的区别：筛选条件的写法（filter:表名.列名 filter_by : 列名）
50 # 注：2）字符拼接时，要加上“synchronize_session=False”
51 # session.query(User).filter(User.id == 6).update({User.name: User.name + "099"}, synchronize_session=False)
52 # session.query(User).filter(User.id == 6).update({"age": User.age + 1}, synchronize_session="evaluate")
53 
54 # 查 -- query(表名或table_name.lie_ming),all() : 把所有结果放在一个列表里 ；first() : 取第一个结果
55 
56 
57 
58 # 只有提交后才能更改数据
59 session.commit()

　　4、一对多

　　注：relationship的用法

 1 Base = declarative_base()
 2 
 3 class Father(Base):
 4     __tablename__ = 'father'
 5     id = Column(Integer,primary_key=True)
 6     name = Column(String(32))
 7     age = Column(Integer)
 8 
 9 class Son(Base):
10     __tablename__ = 'son'
11     id = Column(Integer,primary_key=True)
12     name = Column(String(32))
13     age = Column(Integer)
14     father_id = Column(Integer,ForeignKey('father.id'))
15     father = relationship('Father',backref='sons')
16 
17 Base.metadata.create_all(engine)
18 
19 Session = sessionmaker(bind=engine)
20 session = Session()
21 
22 # f1 = Father(name='huang',age='40')
23 # f2 = Father(name='wang',age = '45')
24 # s1 = Son(name='xiao huang1',age='16')
25 # s2 = Son(name='xiao huang2',age='5')
26 # s3 = Son(name='xiao wang1',age='9')
27 #
28 # session.add_all([f1,f2,])
29 # session.commit()
30 
31 # session.query(Son).filter(Son.id < 3).update({'father_id':1})
32 # session.query(Son).filter(Son.id > 2).update({'father_id':2})
33 
34 # relationship 用法：里面封装了join()连表查询
35 ret = session.query(Son.name,Son.father_id,Son.age,Son.id).filter_by(id=2).all()
36 
37 ret1 = session.query(Son).filter_by(id=2).first()
38 ret2 = session.query(Father).filter_by(id=1).first()
39 print(ret,ret1.father.name,ret2.sons)
40 for i in ret2.sons:
41     print(i.name)
42 
43 
44 # session.add_all([s1,s2,s3,])
45 session.commit()
46 
47 # 问题：1）给列起别名？ 2） 原来的连表查询join的用法？

one VS many

　　5、多对多

　　注：多对多时，利用relationship查询时，建表阶段relationship要加上“secondary=Women_to_Men.__table__” --- Women_to_Men 为连表关系的类名

 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 
 4 from sqlalchemy import create_engine
 5 from sqlalchemy.ext.declarative import declarative_base
 6 from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index
 7 from sqlalchemy.orm import sessionmaker,relationship
 8 import time
 9 
10 engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/day3?charset=utf8")
11 
12 Base = declarative_base()
13 
14 class Women_to_Men(Base):
15     __tablename__ = 'women_to_men'
16     id = Column(Integer,primary_key=True)
17     women_id = Column(Integer,ForeignKey('women.id'))
18     men_id = Column(Integer,ForeignKey('men.id'))
19 
20 class Women(Base):
21     __tablename__ = 'women'
22     id = Column(Integer,primary_key=True)
23     name = Column(String(32))
24     mens = relationship('Men',secondary=Women_to_Men.__table__,backref='womens',)
25 
26 class Men(Base):
27     __tablename__ = 'men'
28     id = Column(Integer,primary_key=True)
29     name = Column(String(32))
30 
31 Base.metadata.create_all(engine)
32 
33 Session = sessionmaker(bind=engine)
34 session = Session()
35 
36 # w1 = Women(name="xiaopao")
37 # w2 = Women(name='xiaoji')
38 # m1 = Men(name='dalong')
39 # m2 = Men(name='dafei')
40 # gx = Women_to_Men(women_id = 1,men_id = 2)
41 # session.add_all([w1,w2,m1,m2])
42 # session.commit()
43 # session.add(gx)
44 
45 women1 = session.query(Women).filter_by(id = 1).first()
46 print(women1,women1.mens)
47 mens = session.query(Men).filter_by(id = 1).first()
48 print(mens,mens.womens)
49 # women1.mens.append(mens)
50 session.commit()
51 
52 # 总结：多对多时，利用relationship查询时，建表阶段relationship要加上“secondary=Women_to_Men.__table__” --- Women_to_Men 为连表关系的类名

many VS many

　　6、查询表的条件

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(text("id<:value and name=:name")).params(value=224, name='fred').order_by(User.id).all()

ret = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

 1 #　条件
 2 ret = session.query(Users).filter_by(name='alex').all()
 3 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
 4 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
 5 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
 6 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
 7 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
 8 from sqlalchemy import and_, or_
 9 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
10 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
11 ret = session.query(Users).filter(
12     or_(
13         Users.id < 2,
14         and_(Users.name == 'eric', Users.id > 3),
15         Users.extra != ""
16     )).all()
17 
18 
19 # 通配符
20 ret = session.query(Users).filter(Users.name.like('e%')).all()
21 ret = session.query(Users).filter(~Users.name.like('e%')).all()
22 
23 # 限制
24 ret = session.query(Users)[1:2]
25 
26 # 排序
27 ret = session.query(Users).order_by(Users.name.desc()).all()
28 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
29 
30 # 分组
31 from sqlalchemy.sql import func
32 
33 ret = session.query(Users).group_by(Users.extra).all()
34 ret = session.query(
35     func.max(Users.id),
36     func.sum(Users.id),
37     func.min(Users.id)).group_by(Users.name).all()
38 
39 ret = session.query(
40     func.max(Users.id),
41     func.sum(Users.id),
42     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()
43 
44 # 连表
45 
46 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
47 
48 ret = session.query(Person).join(Favor).all()
49 
50 ret = session.query(Person).join(Favor, isouter=True).all()
51 
52 
53 # 组合
54 q1 = session.query(Users.name).filter(Users.id > 2)
55 q2 = session.query(Favor.caption).filter(Favor.nid < 2)
56 ret = q1.union(q2).all()
57 
58 q1 = session.query(Users.name).filter(Users.id > 2)
59 q2 = session.query(Favor.caption).filter(Favor.nid < 2)
60 ret = q1.union_all(q2).all()

其他条件帅选