Python3 pymysql 访问MySql数据库
使用pymysql来访问MySQL数据库，注意，有些使用PyMySQL，Python2是mysqldb
如没该module则安装：pip install pymysql
（本人Java转Python，还有java编写习惯，有错误请及时提出，相互交流）
1、定义获取数据库连接的Module，
DataBase.py
import pymysql class MySQLDB: __config={ 'host' : '127.0.0.1', 'port' : 3306, 'user' : 'root', 'password' : 'admin', 'db' : 'test', 'charset' : 'utf8' } #定义了静态方法，类名可直接打点调用 @staticmethod def getConn(): connection = pymysql.connect(**MySQLDB.__config) return connection
2、EmpDao.py 访问数据DAO层
1 from dataBase import DataBase 2 from dataBase import Entity 3 4 class EmpDao: 5 ''' 6 emp表的DAO 7 ''' 8 def saveEmp(self,empEntity): 9 """ 10 保存 11 """ 12 try: 13 self.connection=DataBase.MySQLDB.getConn();#获取数据库连接 14 with self.connection.cursor() as cursor: #获取游标 15 sql = 'INSERT INTO emp (empno,ename,mgr,job,hiredate,sal,comm,deptno) VALUES (%s, %s, %s, %s, %s,%s,%s,%s)' 16 cursor.execute(sql, empEntity) #执行SQL 17 self.connection.commit() #提交事务 18 except Exception as e: 19 print(e) #打印异常 20 finally: 21 self.connection.close()#关闭数据库连接 22 23 24 def selectEmp(self,sqlParam): 25 """ 26 条件查询 27 """ 28 try: 29 self.connection = DataBase.MySQLDB.getConn(); 30 with self.connection.cursor() as cursor: 31 sql='select empno,ename,job,mgr,sal,comm,hiredate,deptno from emp where 1=1 ' 32 if sqlParam: 33 sql=sql+' %s '%(sqlParam) #插入条件sql片段 34 print(sql) 35 cursor.execute(sql) 36 self.rows = cursor.fetchall()#抓取行数据 37 except Exception as e: 38 print(e) 39 finally: 40 self.connection.close() 41 return self.rows 42 43 44 def selectRowCount(self,sqlParam): 45 """ 46 查询总记录数 47 """ 48 try: 49 self.connection=DataBase.MySQLDB.getConn() 50 with self.connection.cursor() as cursor: 51 sql='select count(empno) from emp where 1=1 ' 52 if sqlParam: 53 sql=sql+'%s'%(sqlParam)#sql条件片段 54 print(sql) 55 cursor.execute(sql) 56 self.rows=cursor.fetchall() 57 except Exception as e: 58 print(e) 59 finally: 60 self.connection.close() 61 return self.rows[0][0] 62 63 64 def selectRowsPaper(self,sqlParam=None,pageNow=1,pageSize=5): 65 """ 66 分页查询 67 """ 68 self.pageNow=pageNow 69 self.pageSize=pageSize 70 self.offset=(self.pageNow-1)*self.pageSize #偏移量 71 self.total=self.selectRowCount(sqlParam)#总记录数 72 #总页数 73 self.totalPage= int(self.total/self.pageSize) if self.total%self.pageSize==0 else int(self.total/self.pageSize) +1 74 75 try: 76 self.connection = DataBase.MySQLDB.getConn() 77 with self.connection.cursor() as cursor: 78 sql = 'select empno,ename,job,mgr,sal,comm,hiredate,deptno from emp where 1=1 ' 79 if sqlParam: 80 sql = sql + '%s' % (sqlParam) 81 sql=sql+' limit %s,%s'%(self.offset,self.pageSize) 82 print(sql) 83 cursor.execute(sql) 84 self.rows = cursor.fetchall() 85 return {'total':self.total,'pageNow':self.pageNow,'totalPage':self.totalPage,'rows':self.rows} 86 except Exception as e: 87 print(e)
3、测试
（1）新增
1 if __name__ == '__main__': 2 empDao =EmpDao( 3 empDao.saveEmp((116, 'ZYG', 7989, 'Teacher', '1999-10-10', 23000, None, 20))
（2）分页查询
if __name__ == '__main__': empDao =EmpDao() #分页查询 res=empDao.selectRowsPaper(pageNow=2) print(res) s=res['rows'] for r in s: print('编号：%s,姓名:%s,入职日期：%s,工资：%s,部门：%s' % (r[0], r[1], r[6], r[4], r[7]))
通过字典形式封装，拼接sql：
1 #mysql新增操作 2 import pymysql 3 data={ 4 "post_uuid":'10100', 5 "post_name":'1', 6 'organ_uuid':10, 7 'moddate':'20101010', 8 'user_uuid':'1', 9 'role_name':'科员101', 10 'remark':'无' 11 } 12 tableName='pt_post' 13 columns=','.join(data.keys()) 14 values=','.join(['%s']*len(data)) 15 sql= 'insert into {table}({keys}) VALUES ({values}) '.format(table=tableName, keys=columns, values=values) 16 print(sql) 17 db = pymysql.connect(host='localhost', user='root',password='admin', port=3306, db ='test') 18 print(tuple(data.values())) 19 cursor = db. cursor() 20 cursor.execute(sql,tuple(data.values())) 21 db.commit()
