python与数据库————python调用MySQL数据库与SQL的工具类

基本连接方式

# 导入mysql数据库
import pymysql
try:
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",
                       password="root",db="db_mysql",charset='utf8')
    # 获取游标对象
    cursor = conn.cursor()

    # 执行SQL,如果是增删改，execute会返回影响的行数
    # 不要手动拼接sql，会引发一个sql注入漏洞！

except Exception as e:
    print("失败："+e)
    # 出错后回滚数据
    conn.rollback()
finally:
    # 关闭资源
    cursor.close()
    conn.close()

查询数据

	# 输出emp表的数据条
	count = cursor.execute("select * from emp")
    	print(count)
    
    # 如果是查询，需要再执行一行代码
    data1=  cursor.fetchone()#查询一行数据
    print(data1)
    data =  cursor.fetchall()#查询全部数据
    print(data)
# fetchall   #查询全部数据，返回值是一个二维元组，它的元素是一个元组，表示一条记录
# fetchone   #查询一条数据，返回值是一个元组，表示一行数据

# 结果
7
(1, '张三', 20, 1)
((2, '李四', 18, 2), (3, '王五', 30, 3), (4, '赵六', 50, 3), (5, '翠花', 22, 3), (6, 'wb', 30, 3), (7, 'wyb', 23, None))

插入、修改、删除数据

	# 增删改操作必须要提交事务
    sql = cursor.execute("insert into emp values(8,'马保国',69,3)")
    # 手动提交事物
    conn.commit()
    if sql == 1:
        print("陈坤")
    else:
        print("失败")
    print(sql)

SQL的工具类

import pymysql

class SQLHeler(object):
    def __init__(self,user,password,db,host="localhost",port=3306,charset="utf8"):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset
    def __connection(self):
        self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,\
                                    password=self.password,db=self.db,charset=self.charset)
        self.cursor = self.conn.cursor()

    ##增删改
    def update(self,sql,params=None):
        try:
            self.__connection()
            count = self.cursor.execute(sql,params)
            self.conn.commit()
            return count
        except Exception as e:
            print("出现错误：",e)
            self.conn.rollback()
        finally:
            self.__closeResource()#释放资源

    #查询一条
    def queryOne(self,sql,params=None):
        try:
            self.__connection()
            count = self.cursor.execute(sql,params)
            data = self.cursor.fetchone()
            return count,data
        finally:
            self.__closeResource()

    #查询所有
    def queryAll(self,sql,params=None):
        try:
            self.__connection()
            count = self.cursor.execute(sql,params)
            data = self.cursor.fetchall()
            return count,data
        finally:
            self.__closeResource()
    #释放资源
    def __closeResource(self):
        if self.cursor:
            self.cursor.close()
        if self.conn is not None:
            self.conn.close()

测试

from sqlHelper import SQLHeler

sqlhelper=SQLHeler(user="root",password="root",db="db_mysql")

#增
# count = sqlhelper.update("insert into emp values(%s,%s,%s,%s,%s,%s,NULL,%s)",\
#                  [1018,"马老师",'厨师',1001,'2020-12-4',200 ,50])
# if count:
#     print("插入陈坤")
# else:
#     print("这波出点小司误")

#删
# sql = "delete from emp where empno=%s"
# params = (1019)
# count = sqlhelper.update(sql,params)
# if count:
#     print("删除陈坤")
# else:
#     print("这波出点小司误")

#改
# sql = "update emp set mgr=%s,sai=%s where empno=%s"
# params = (1111,8000,1018)
# count = sqlhelper.update(sql,params)
# if count:
#     print("修改陈坤")
# else:
#     print("这波出点小司误")

#查
# sql="select * from emp where empno=%s"
# params=(1018)
# count ,user= sqlhelper.queryOne(sql,params)
# print("共查询到{}个用户".format(count))
# print(user)

# count ,user = sqlhelper.queryOne("select * from emp where empno=%s",[1018])
# print("共查询到{}个用户".format(count))
# print(user)


sql="select * from emp"
params=None
count ,user= sqlhelper.queryAll(sql,params)
print("共查询到{}个用户".format(count))
print(user)

# 结果
共查询到8个用户
((1, '张三', 20, 1), (2, '李四', 18, 2), (3, '王五', 30, 3), (4, '赵六', 50, 3), (5, '翠花', 22, 3), (6, 'wb', 30, 3), (7, 'wyb', 23, None), (8, '马保国', 69, 3))