Python与MySQL和ORM_SQLALChemy

安装引入

python3安装pymysql

pip install pymysql

引入pymysl

import pymysql

Connection对象

Connection对象用于建立与数据库的连接
创建对象：调用connect()方法

参数host：连接的mysql主机，如果本机是'localhost'
参数port：连接的mysql主机的端口，默认是3306
参数db：数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，默认是'gb2312'，要求与数据库创建时指定的编码一致，否则中文会乱码

对象的方法

close()关闭连接
commit()事务，所以需要提交才会生效
rollback()事务，放弃之前的操作
cursor()返回Cursor对象，用于执行sql语句并获得结果

Cursor对象

创建对象：调用Connection对象的cursor()方法

cursor1=conn.cursor()

对象的方法

close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
fetchmany(n)执行查询时，获取n行，一行构成一个元组，再将这些元组装入一个元组返回
next() 执行查询语句时，获取当前行的下一行()
scroll(value[,mode])将行指针移动到某个位置
mode表示移动的方式
mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动
mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0
对象的属性
rowcount只读属性，表示最近一次execute()执行后受影响的行数

CURD（Create Update Read Delete）

create

创建testInsert.py文件，向学生表中插入一条数据

import pymysql
try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cs1=conn.cursor()
count=cs1.execute("insert into students(sname) values('张良')")
print count
conn.commit()
cs1.close()
conn.close()
except Exception as e:
conn.rollback()
print(e)

update

创建testUpdate.py文件，修改学生表的一条数据

import MySQLdb
try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cs1=conn.cursor()
count=cs1.execute("update students set sname='刘邦' where id=6")
print count
conn.commit()
cs1.close()
conn.close()
except Exception,e:
conn.rollback()
print(e)

read

创建testSelectOne.py文件，查询一条学生信息

import pymysql
try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cur=conn.cursor()
cur.execute('select * from students where id=7')
result=cur.fetchone()
result=cur.fetchall()
print(result)
cur.close()
conn.close()
except Exception as e:
print(e)

delete

创建testDelete.py文件，删除学生表的一条数据

import MySQLdb
try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cs1=conn.cursor()
count=cs1.execute("delete from students where id=6")
print count
conn.commit()
cs1.close()
conn.close()
except Exception,e:
print(e)

语句参数化

#format
# row = cursor.execute("select * from customer where id>{} and addr='{}'".format(2, 'henan'))
# %
# row = cursor.execute("select * from customer where id>%s and addr='%s'" % (2, 'henan'))
# args
# row = cursor.execute("select * from customer where id>%s and addr=%s", (2, 'henan'))

封装

"""
mysql 辅助类
"""
import pymysql
class MySQLHelper(object):
def __init__(self, database, host="localhost", port=3306, user="root", password="123456", charset='utf8'
):
self.host = host
self.port = port
self.user = user
self.password = password
self.database = database
self.charset = charset
self.con = None
self.cursor = None
self.__connect()
def __connect(self):
try:
self.con = pymysql.connect(host=self.host, user=self.user, password=self.password,
database=self.database, port=self.port, charset=self.charset)
self.cursor = self.con.cursor()
except Exception as e:
print(e)
finally:
pass
def queryOne(self, queryStr, args=None):
try:
row = self.cursor.execute(queryStr, args)
if row > 0:
return self.cursor.fetchone()
else:
return None
except Exception as e:
print(e)
finally:
self.__close()
def queryMany(self, queryStr, args=None):
try:
self.__connect()
row = self.cursor.execute(queryStr, args)
if row > 0:
return self.cursor.fetchall()
else:
return None
except Exception as e:
print(e)
finally:
self.__close()
def update(self, updateStr, args=None):
try:
self.__connect()
row = self.cursor.execute(updateStr, args)
self.con.commit()
if row > 0:
return "Success"
else:
return "Faield"
except Exception as e:
print(e)
self.con.rollback()
finally:
self.__close()
def __close(self):
if self.cursor is not None:
self.cursor.close()
if self.con is not None:
self.con.close()

用户登录案例

创建用户表
创建用户表userinfos表结构如下
id uname upwd
注意：需要对密码进行加密
如果使用md5加密，则密码包含32个字符
如果使用sha1加密，则密码包含40个字符，推荐使用这种方式
create table userinfos(id int primary key auto_increment,uname varchar(20),upwd char(40));
加入数据
加入测试数据
插入如下数据，用户名为123,密码为123,这是sha1加密后的值
insert into userinfos values(0,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0);
接受输入并验证
接收输入并验证
创建testLogin.py文件，引入hashlib模块、MysqlHelper模块
接收输入
根据用户名查询，如果未查到则提示用户名不存在
如果查到则匹配密码是否相等，如果相等则提示登录成功
如果不相等则提示密码错误

from MysqlHelper import MysqlHelper
from hashlib import sha1
sname=input("请输入用户名：")
spwd=input("请输入密码:")
s1=sha1()
s1.update(spwd)
spwdSha1=s1.hexdigest()
sql="select upwd from userinfos where uname=%s"
params=[sname]
sqlhelper=MysqlHelper('localhost',3306,'test1','root','mysql')
userinfo=sqlhelper.get_one(sql,params)
if userinfo==None:
print\('用户名错误'\)
elif userinfo[0]==spwdSha1:
print\('登录成功'\)
else:
print\('密码错误'\)

ORM_SQLALChemy

ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。操作数据库像操作对象一样，是不是很简
单？
但是由谁来做这个转换呢？所以ORM框架应运而生。
在Python中，最有名的ORM框架是SQLAlchemy。

框架安装

需要安装sqlalchemy以及mysql-connector-python两个模块

操作流程

引入模块

import sqlalchemy
# print(sqlalchemy.__version__)

创建连接实例

from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:123456@localhost/sqlalchemytest")
# result = engine.execute("show tables")
# print(result.fetchall())

创建会话

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine); #通过bind 参数使Session连接到数据库
# print(Session)

创建对应于MySql数据表的类

#新建类
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String #需要列的类型
Base = declarative_base() #Base为所有自定一类的父类
class Test(Base):
__tablename__ = "test" #必须写 决定于mysql那个表关联
id = Column(Integer, primary_key=True) # 关联到表中id列
name = Column(String(45))

与数据库交互

session = Session()
#查询
result = session.query(Test.id,Test.name).filter(Test.id==1).first()
print(result)
29
#添加
# session.add( Test(id=0,name='wangzhenxing'))
# session.commit()
#修改
# t5 = session.query(Test).filter(Test.id == 5).first()
# t5.name="caoxiang"
# session.commit()
# session.query(Test).filter(Test.id == 4).update({Test.name:"456" })
# session.commit()
#删除
# t4 = session.query(Test).filter(Test.id==4).first()
# session.delete(t4)
# session.commit()
# session.query(Test).filter(Test.id==2).delete()
# session.commit()