Python环境下MySQL数据库的操作

在Python环境下连接MySQL数据库，一般有两种方式：
1、在Python环境中连接MySQL数据库后，实际提取数据用的还是SQL语句。常用的有：pymysql库；
这种编程语言在不同的数据库间一般不具有通用性，如果换个数据库，又得把代码全部重新写。
2、运用ORM框架，构建关系数据库的表结构到对象上的映射，常用的实现ORM框架有SQLAlchemy库。
ORM框架在数据库间的迁移比较方便，框架内部语言写好后，切换数据库很方便，不需要从头再写，只需要修改连接即可。

下面对上述提到的两个包进行简单的介绍：

import pymysql

#连接数据库
conn= pymysql.connect(host='localhost',port=3306,user='root',passwd='0000',db='test',charset='utf8')
#自动提交
conn.autocommit(True)
#创建游标
cursor=conn.cursor()

#创建数据表
sqltext='create table users(id int primary key auto_increment,name varchar(20) not null,address varchar(20),email varchar(20) unique)'
cursor.execute(sqltext)

#关闭游标
cursor.close()
#关闭连接
conn.close()

#以上连接数据库，创建游标，及后续关闭游标，关闭连接操作都是必须的，关闭后才能提交sql语句。下面操作因篇幅问题把这部分省略了，但实际执行的时候必须要加上。

#插入数据
sqltext='insert into users(name,address,email) values("dasheng","beijing","abc111@def")'
cursor.execute(sqltext)

#删除数据
sqltext='delete from users where email="abc111@def"'
cursor.execute(sqltext)

#查询数据：返回的是元组。
sqltext='select * from users'
cursor.execute(sqltext)
for row in cursor:
    print(row)

#查询数据，限制条数
sqltext=('select * from users')
cursor.execute(sqltext)
for row in cursor.fetchmany(1):
    print(row)

#更改字段数据类型
sqltext='alter table users change address address varchar(200)'
cursor.execute(sqltext)

#批量插入数据：
#生成伪数据：
from faker import Factory
#生成器
userfaker=Factory.create()
userinfo=[(userfaker.name(),userfaker.address(),userfaker.email()) for i in range(10)]

#批量增加数据
sql_template='insert into users(name,address,email) values(%s,%s,%s)'
cursor.executemany(sql_template,userinfo)
cursor.close()
conn.close()

pymysql的运用还是比较简单的，只要会写mysql语句，实现起来很容易。

下面讲讲ORM框架：SQLAlchemy的用法。

#ORM框架SQLAlchemy：把关系数据库的表结构映射到对象上
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,DateTime,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship,backref

#1.创建连接
engine=create_engine('mysql+pymysql://root:0000@127.0.0.1:3306/testdb?charset=utf8',echo=True)  #echo打印sql语句

#2.定义映射
Base = declarative_base()  #定义父类
#定义User对象
class User(Base):
    #定义表名
    __tablename__='users'
    #定义表结构
    id=Column(Integer,primary_key=True)
    name=Column(String(40))
    #外键
    orders=relationship('Order')

class Product(Base):
    __tablename__='products'
    
    id=Column(Integer,primary_key=True)
    name=Column(String(40))

    orders=relationship('Order')
    
class Order(Base):
    __tablename__='orders'
    
    id=Column(Integer,primary_key=True)
    otime=Column(DateTime)
    #定义外键
    uid=Column(Integer,ForeignKey('users.id'))
    pid=Column(Integer,ForeignKey('products.id'))

#3.映射实例化，创建数据库表
Base.metadata.create_all(engine)

#4.创建会话
Session=sessionmaker(engine)
session=Session()

#5.对象实例持久化
#新建用户
new_user=User(name='xiaoming')
#添加到session
session.add(new_user)
new_p=Product(name='p1')
session.add(new_p)
#提交即保存到数据库
session.commit()  #提交

#可见，关键是获取session，然后把对象添加到session，最后提交并关闭。Session对象可视为当前数据库连接。

#查找
#创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行
xiaoming=session.query(User).filter(User.name=='xiaoming').one()
#返回的是一个字典对象
xiaoming.name

p1=session.query(Product).filter(Product.name=='p1').one()
p1.id
p1.name

#添加order数据(外键的添加方法)
o1 = Order(uid=xiaoming.id,pid=p1.id)
session.add(o1)
session.commit()

#跨表查询
orders=xiaoming.orders
for order in orders:
    print(order.id)
#关闭session
session.close()