用Python操作数据库

1.安装引入模块

安装mysql模块，在windows和ubuntu中

windows里安装mysql模块

Linux里安装mysql模块

在文件中引入模块import pymysql

2.Connection对象

用于建立与数据库的连接创建对象：调用connect()方法conn=connect(参数列表)参数host：连接的mysql主机，如果本机是‘localhost‘参数port：连接的mysql主机的端口，默认是3306参数db：数据库的名称参数user：连接的用户名参数password：连接的密码参数charset：通信采用的编码方式，默认是‘gb2312‘，要求与数据库创建时指定的编码一致，否则中文会乱码

3.对象的方法

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

Cursor对象

执行sql语句创建对象：调用Connection对象的cursor()方法cursor1=conn.cursor()

对象的方法

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

举例便于理解

# 导入python操作mysql的模块import pymysql# 获取连接对象conn = pymysql.connect(host=‘127.0.0.1‘, user=‘root‘, password=‘123456‘, database=‘python01‘, port=3306, charset=‘utf8‘)# 获取执行工具cur = conn.cursor()# sql语句,增删改#sql = ‘ｓｅｌｅｃｔ birthday from t_user‘sql = ‘ｓｅｌｅｃｔ id,name,pwd,birthday from t_user‘# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数count = cur.execute(sql)print(‘查询的结果有%s条数据‘%count)# 获取第一行dateOne = cur.fetchone()print(dateOne)# 向上移动一行cur.scroll(-1)# 向下移动一行cur.scroll(1)cur.scroll(1,mode=‘absolute‘) 绝对的，这里指的是第一行cur.scroll(1,mode=‘relative‘) 相对的#获取所有行的数据dataAll = cur.fetchall()print(dataAll)for temp in dataAll: print(temp) print(dataAll[-1][2]) #dataAll[-1]得到的是一个用户所有的信息，dataAll[-1][2]获取最后一个人的密码for temp in cur: print(temp)s = ‘id:%s,name:%s,pwd:%s,birthday:%s‘for temp in dataAll: print(s%(temp[0],temp[1],temp[2],temp[3]))# 关闭cur.close()conn.close()

4.对象的属性

rowcount只读属性，表示最近一次execute()执行后受影响的行数connection获得当前连接对象

增删改查

1.增

创建testInsert.py文件，向学生表中插入一条数据#encoding=utf-8import pymysqltry: conn=pymysql.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘) cs1=conn.cursor() count=cs1.execute("ｉｎｓｅｒｔ into students(sname) values(‘张良‘)") print(count) conn.commit() cs1.close() conn.close()except Exception,e: print(e)

2.修改

创建testUpdate.py文件，修改学生表的一条数据#encoding=utf-8import pymysqltry: conn=pymysql.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘) cs1=conn.cursor() count=cs1.execute("ｕｐｄａｔｅ students set sname=‘刘邦‘ where id=6") print(count) conn.commit() cs1.close() conn.close()except Exception,e: print(e)

3.删除

创建testDelete.py文件，删除学生表的一条数据#encoding=utf-8import pymysqltry: conn=pymysql.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘) cs1=conn.cursor() count=cs1.execute("ｄｅｌｅｔｅ from students where id=6") print(count) conn.commit() cs1.close() conn.close()except Exception as e: print(e)

4.查

创建testSelectOne.py文件，查询一条学生信息import Pymysqltry: conn=Pymysql.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘) cur=conn.cursor() cur.execute(‘ｓｅｌｅｃｔ * from students where id=7‘) result=cur.fetchone() print result cur.close() conn.close()except Exception as e: print(e)创建testSelectMany.py文件，查询全部学生信息#encoding=utf8import Pymysqltry: conn=Pymysql.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘) cur=conn.cursor() cur.execute(‘ｓｅｌｅｃｔ * from students‘) result=cur.fetchall() print result cur.close() conn.close()except Exception as e: print(e)

实例一：参数

# 导入python操作mysql的模块import pymysqlimport time# 获取连接对象conn = pymysql.connect(host=‘127.0.0.1‘, user=‘root‘, password=‘123456‘, database=‘python01‘, port=3306, charset=‘utf8‘)# 获取执行工具cur = conn.cursor()# sql语句,增删改，sql注入sql = ‘ｉｎｓｅｒｔ into t_user(name,pwd,birthday) values(%s,%s,%s)‘# 参数列表name = input(‘输入姓名:‘)pwd = input(‘输入密码:‘)birthday = input(‘输入生日:‘) # 2017年10月01日-->日期struct_time(--->2017-10-01)birthday = time.strptime(birthday,‘%Y年%m月%d日‘) #这里我们就用到了时间与字符串的相互转换(详情见MySQL高级)params = [name,pwd,birthday]# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数count = cur.execute(sql,params)#提交conn.commit()print(‘受影响的行数:%s‘%count)# 关闭cur.close()conn.close()

实例二：抛出异常

# 导入python操作mysql的模块import pymysqltry: conn = None cur = None # 获取连接对象 conn = pymysql.connect(host=‘127.0.0.1‘, user=‘root‘, password=‘123456‘, database=‘python01‘, port=3306, charset=‘utf8‘) # 模拟异常 # a = 1 / 0 # 获取执行工具 cur = conn.cursor() # sql语句,增删改 sql = ‘ｉｎｓｅｒｔ into t_user(name,pwd,birthday) values("小伊","123456",str_to_date("2017年10月20日","%Y年%m月%d日"))‘ # 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数 count = cur.execute(sql) # 提交 conn.commit() print(‘受影响的行数:%s‘ % count)except Exception as ex: # 打印异常信息，测试时候使用，项目上线，去掉 print(str(ex)) # 将异常继续抛出 # raisefinally: if cur != None: cur.close() if conn != None: conn.close()

实例三：

# 导入python操作mysql的模块import pymysql# 获取连接对象conn = pymysql.connect(host=‘127.0.0.1‘, user=‘root‘, password=‘123456‘, database=‘python01‘, port=3306, charset=‘utf8‘)# 获取执行工具cur = conn.cursor()# sql语句,增删改#sql = ‘ｓｅｌｅｃｔ birthday from t_user‘sql = ‘ｓｅｌｅｃｔ id,name,pwd,birthday from t_user‘# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数count = cur.execute(sql)print(‘查询的结果有%s条数据‘%count)# 获取第一行# dateOne = cur.fetchone()# print(dateOne)# for temp in cur:# print(temp)s = ‘id:%s,name:%s,pwd:%s,birthday:%s‘for temp in dataAll: print(s%(temp[0],temp[1],temp[2],temp[3]))# 关闭cur.close()conn.close()

封装

我们可以封装一个py文件，方便每次进行调用。把这个库定义为：mySqlHelper

"""python操作mysql进行增删改查的封装1、增删改，代码类似2、查询代码分析1、获取连接对象2、sql语句不同，参数不同3、获取执行对象"""import pymysqlclass MysqlHelper: ‘‘‘python操作mysql的增删改查的封装‘‘‘ def __init__(self, host, user, password, database, port=3306, charset=‘utf8‘): ‘‘‘ 初始化参数 :param host: 主机 :param user: 用户名 :param password: 密码 :param database: 数据库 :param port: 端口号，默认是3306 :param charset: 编码，默认是utf8 ‘‘‘ self.host = host self.port = port self.database = database self.user = user self.password = password self.charset = charset def connect(self): ‘‘‘ 获取连接对象和执行对象 :return: ‘‘‘ self.conn = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database, port=self.port, charset=self.charset) self.cur = self.conn.cursor() def fetchone(self, sql, params=None): ‘‘‘ 根据sql和参数获取一行数据 :param sql: sql语句 :param params: sql语句对象的参数元组，默认值为None :return: 查询的一行数据 ‘‘‘ dataOne = None try: count = self.cur.execute(sql, params) if count != 0: dataOne = self.cur.fetchone() except Exception as ex: print(ex) finally: self.close() return dataOne def fetchall(self, sql, params=None): ‘‘‘ 根据sql和参数获取一行数据 :param sql: sql语句 :param params: sql语句对象的参数列表，默认值为None :return: 查询的一行数据 ‘‘‘ dataall = None try: count = self.cur.execute(sql, params) if count != 0: dataall = self.cur.fetchall() except Exception as ex: print(ex) finally: self.close() return dataall def __item(self, sql, params=None): ‘‘‘ 执行增删改 :param sql: sql语句 :param params: sql语句对象的参数列表，默认值为None :return: 受影响的行数 ‘‘‘ count = 0 try: count = self.cur.execute(sql, params) self.conn.commit() except Exception as ex: print(ex) finally: self.close() return count def ｕｐｄａｔｅ(self, sql, params=None): ‘‘‘ 执行修改 :param sql: sql语句 :param params: sql语句对象的参数列表，默认值为None :return: 受影响的行数 ‘‘‘ return self.__item(sql, params) def ｉｎｓｅｒｔ(self, sql, params=None): ‘‘‘ 执行新增 :param sql: sql语句 :param params: sql语句对象的参数列表，默认值为None :return: 受影响的行数 ‘‘‘ return self.__item(sql, params) def ｄｅｌｅｔｅ(self, sql, params=None): ‘‘‘ 执行删除 :param sql: sql语句 :param params: sql语句对象的参数列表，默认值为None :return: 受影响的行数 ‘‘‘ return self.__item(sql, params) def close(self): ‘‘‘ 关闭执行工具和连接对象 ‘‘‘ if self.cur != None: self.cur.close() if self.conn != None: self.conn.close()

测试

1.测试查询多条数据

import mysqlHelper# 初始化对象helper = mysqlHelper.MysqlHelper(‘127.0.0.1‘, ‘root‘, ‘123456‘, ‘python01‘)# 连接helper.connect()# sqlsql = ‘ｓｅｌｅｃｔ * from t_user where name = %s and id > %s‘# paramsparams = [‘小茗‘,1]# 执行data = helper.fetchall(sql, params)# 判断if data: for temp in data: print(temp)else: # None,False,0 print(‘没有数据.‘)helper.close()

2.测试查询一条数据

import mysqlHelper# 初始化对象helper = mysqlHelper.MysqlHelper(‘127.0.0.1‘, ‘root‘, ‘123456‘, ‘python01‘)# 连接helper.connect()# sqlsql = ‘ｓｅｌｅｃｔ * from t_user where id = %s‘#sql = ‘ｓｅｌｅｃｔ * from t_user where id = 1‘# paramsparams = [2]# 执行data = helper.fetchone(sql, params)#data = helper.fetchone(sql)# 判断if data: print(data)else: # None,False,0 print(‘没有数据.‘)

3.测试增删改

import mysqlHelperimport time# 初始化对象helper = mysqlHelper.MysqlHelper(‘127.0.0.1‘, ‘root‘, ‘123456‘, ‘python01‘)# 连接helper.connect()# sqlsql = ‘ｕｐｄａｔｅ t_user set name =%s,pwd=%s,birthday=%s where id=%s‘# paramsid = input(‘输入编号:‘)name = input(‘输入姓名:‘)pwd = input(‘输入密码:‘)birthday = time.strptime(input(‘输入生日:‘), ‘%Y年%m月%d日‘)params = [name, pwd, birthday,id]# 执行count = helper.ｕｐｄａｔｅ(sql, params)# 判断if count: print(‘操作成功.‘)else: # None,False,0 print(‘操作失败.‘)

4.简单的登录和注册

做项目的时候会有个尝试登录和注册的时候需要对密码进行加密进行存储，刚好结合sql语句进行一个简单的交互。

注意：

需要对密码进行加密如果使用md5加密，则密码包含32个字符如果使用sha1加密，则密码包含40个字符，这里使用这种方式create table userinfos( id int primary key auto_increment, uname varchar(20), upwd ｃｈａｒ(40), isdelete bit default 0);/*ret = doPwd(‘123‘)print(ret)结果：40bd001563085fc35165329ea1ff5c5ecbdbbeef */-- 插入如下数据，用户名为123,密码为123,这是sha1加密后的值ｉｎｓｅｒｔ into userinfos values(1,‘123‘,‘40bd001563085fc35165329ea1ff5c5ecbdbbeef‘,0);

登录与注册

import hashlibfrom mysqlHelper import MysqlHelperdef login(): ‘‘‘登录‘‘‘ name = input(‘输入用户名:‘) pwd = input(‘输入密码:‘) #加密 pwd = doPwd(pwd) helper = MysqlHelper(‘127.0.0.1‘, ‘root‘, ‘123456‘, ‘python01‘) helper.connect() sql = ‘ｓｅｌｅｃｔ * from t_user where name=%s and pwd=%s‘ params = [name, pwd] data = helper.fetchone(sql, params) if data: print(‘登录成功.‘) else: # None,False,0 print(‘登录失败.‘)def doPwd(pwd): ‘‘‘sha1编码‘‘‘ mysha1 = hashlib.sha1() mysha1.ｕｐｄａｔｅ(pwd.encode(‘utf-8‘)) pwd = mysha1.hexdigest() return pwddef register(): ‘‘‘注册‘‘‘ name = input(‘输入用户名:‘) pwd = input(‘输入密码:‘) # 加密 pwd = doPwd(pwd) helper = MysqlHelper(‘127.0.0.1‘, ‘root‘, ‘123456‘, ‘python01‘) helper.connect() sql = ‘ｉｎｓｅｒｔ into t_user(name,pwd) values(%s,%s)‘ params = [name, pwd] count = helper.ｉｎｓｅｒｔ(sql, params) if count: print(‘操作成功.‘) else: # None,False,0 print(‘操作失败.‘)if __name__ == ‘__main__‘: #register() login()

实际项目中的登录注册往往加入了各种验证，这里的代码只是一个简单的登录注册，只为了学习交流。

来源网络，侵权联系删除