python 链接 mysql

普通连接方法

"""
    使用python调用MYSQL
    1.导入对应模块（驱动）
    2.创建连接网络
    3.获取游标
    4.执行SQL语句
    5.封装数据（看情况而定）
    6.释放资源
"""
#1.导入对应相应模块
import pymysql

#2.创建连接网络     本机IP地址也可以为localhost
conn = pymysql.connect(host = "localhost",port=3306, db="db_py1801b", user = "root", password = "root",charset="utf8")

#3.获取游标
cursor = conn.cursor()

#4.执行SQL语句
#注意不要手动拼接sql，容易注入sql漏洞
cursor.execute("select * from stu")
# cursor.execute("select * from stu where id = %s",2)

#5.获取结果集
#返回的集为一个元组
# users = cursor.fetchall()
users = cursor.fetchone()
print(users)
# try:
#   str = "update stu set name=%s, tel=%s where id = %s"
#   cursor.execute(str,["阳阳","123",3])               #转化成为一个数组
#
#   #提交数据
#   conn.commit()
# except Exception as e:
#   #回滚数据
#   conn.rollback()
#   print("出现错误",e )

#6.关闭资源
cursor.close()
conn.close()

面向对象连接方法

"""
    SQLHelper模块，主要作为进行sql查询封装
    减少重复代码的出现
"""

#引用 pymysql 模块
import pymysql

class Sqlhelper():
    """
    host, poot, db, user, pwd charset
    """
    def __init__(self, host, port, db, user, pwd, charset = "utf8"):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.pwd = pwd
        self.charset = charset
        self.conn = None
        self.cursor = None
        
    #获取连接的方法
    def connection(self):
        #连接mysql数据库的固定语句
        self.conn = pymysql.connect(host = self.host, port = self.port,
                                    db = self.db, user = self.user, password = self.pwd, charsrt = self.charset)
        self.cursor = self.conn.cursor()
        
    #查询一条数据
    def queryOne(self, sql, params):
        try:
            self.connection()
            self.cursor.execute(sql, params)
            return self.cursor.fetchone()
        except Exception as e:
            print("该条信息错误:", e)
            
        # 引用关闭函数
        finally:
            self.closed()
        return None

    def queryAll(self, sql, params):
        try:
            self.connection()
            self.cursor.execute(sql,params)
            return self.cursor.fecthall()
        except Exception as e:
            print("该条信息错误:", e)
            
        # 引用关闭函数
        finally:
            self.closed()
        return None
        
    
    def update(self, sql, params):
        try:
            self.connection()
            count = self.cursor.execute(sql,params)
            #提交数据
            self.conn.commit()
            return count
        except Exception as e:
            #发生异常，数据回滚
            self.cursor.rollblak()
            print("该条信息错误:", e)
        
        #引用关闭函数
        finally:
            self.closed()
        return None

    def closed(self):
        if self.cursor != None:
            self.cursor.close()
        if self.conn != None:
            self.conn.close()