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层

from dataBase import DataBase
from dataBase import Entity
 
class EmpDao:
    '''
    emp表的DAO
    '''
    def saveEmp(self,empEntity):
        """
        保存
        """
        try:
            self.connection=DataBase.MySQLDB.getConn();#获取数据库连接
            with self.connection.cursor() as cursor: #获取游标
                sql = 'INSERT INTO emp (empno,ename,mgr,job,hiredate,sal,comm,deptno) VALUES (%s, %s, %s, %s, %s,%s,%s,%s)'
                cursor.execute(sql, empEntity) #执行SQL
                self.connection.commit() #提交事务
        except Exception as e:
            print(e) #打印异常
        finally:
            self.connection.close()#关闭数据库连接


    def selectEmp(self,sqlParam):
        """
        条件查询
        """
        try:
            self.connection = DataBase.MySQLDB.getConn();
            with self.connection.cursor() as cursor:
                sql='select empno,ename,job,mgr,sal,comm,hiredate,deptno from emp where 1=1  '
                if sqlParam:
                    sql=sql+' %s '%(sqlParam) #插入条件sql片段
                print(sql)
                cursor.execute(sql)
                self.rows = cursor.fetchall()#抓取行数据  
        except Exception as e:
             print(e)
        finally:
            self.connection.close()
        return self.rows


    def selectRowCount(self,sqlParam):
        """
        查询总记录数
        """
        try:
            self.connection=DataBase.MySQLDB.getConn()
            with self.connection.cursor() as cursor:
                sql='select count(empno) from emp where 1=1 '
                if sqlParam:
                    sql=sql+'%s'%(sqlParam)#sql条件片段
                print(sql)
                cursor.execute(sql)
                self.rows=cursor.fetchall()
        except Exception as e:
            print(e)
        finally:
            self.connection.close()
        return self.rows[0][0]


    def selectRowsPaper(self,sqlParam=None,pageNow=1,pageSize=5):
        """
        分页查询
        """
        self.pageNow=pageNow
        self.pageSize=pageSize
        self.offset=(self.pageNow-1)*self.pageSize #偏移量
        self.total=self.selectRowCount(sqlParam)#总记录数
        #总页数
        self.totalPage= int(self.total/self.pageSize)  if self.total%self.pageSize==0 else int(self.total/self.pageSize) +1

        try:
            self.connection = DataBase.MySQLDB.getConn()
            with self.connection.cursor() as cursor:
                sql = 'select  empno,ename,job,mgr,sal,comm,hiredate,deptno from emp where 1=1 '
                if sqlParam:
                    sql = sql + '%s' % (sqlParam)
                sql=sql+' limit %s,%s'%(self.offset,self.pageSize)
                print(sql)
                cursor.execute(sql)
                self.rows = cursor.fetchall()
                return {'total':self.total,'pageNow':self.pageNow,'totalPage':self.totalPage,'rows':self.rows}
        except Exception as e:
            print(e)

3、测试

（1）新增

if __name__ == '__main__':
    empDao =EmpDao(
    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：

#mysql新增操作
import pymysql
data={
    "post_uuid":'10100',
    "post_name":'1',
    'organ_uuid':10,
    'moddate':'20101010',
    'user_uuid':'1',
    'role_name':'科员101',
    'remark':'无'
}
tableName='pt_post'
columns=','.join(data.keys())
values=','.join(['%s']*len(data))
sql=    'insert into {table}({keys}) VALUES ({values}) '.format(table=tableName, keys=columns, values=values)
print(sql)
db = pymysql.connect(host='localhost', user='root',password='admin', port=3306, db ='test')
print(tuple(data.values()))
cursor = db. cursor()
cursor.execute(sql,tuple(data.values()))
db.commit()