import pymysql
class my_sql:
def __init__(self,host,database,user,password,port=3306):
"""
:param host: # 我们要连接的主机ip地址 如果是本机就填localhost或者127.0.0.1 网络地址则输入对应ip
:param database: # database 输入我们想要操作的数据库
:param user: # 数据库账号 默认为root
:param password: # 数据库密码
:param port: # 端口默认是3306 如果更换 请填写更换后的端口号
"""
try:
self.con = pymysql.connect(
host=host,
database=database,
port=port,
user=user,
password=password,
cursorclass=pymysql.cursors.DictCursor # 一行数据是字典形式。多行数据是列表中嵌套字典.
)
except :
raise
else:
# 建立游标
self.cur = self.con.cursor()
def get_count_query(self,select_sql,args=None):
count =self.cur.execute(select_sql,args)
return count
def get_quert_sql_result(self,select_sql,args=None,size=1):
"""
:param select_sql:
:param args:
:param size: size = 1表示fetchone size = -1表示fetchall size>1表示fetchmany
:return:
"""
self.cur.execute(select_sql,args)
if size == 1 :
return self.cur.fetchone()
elif size == -1 :
return self.cur.fetchall()
elif size >1:
return self.cur.fetchmany(size)
def update_sql(self,update_sql,args=None):
try:
self.cur.execute(update_sql,args)
except :
self.con.rollback()
else:
self.con.commit()
def colse(self):
self.cur.close()
self.con.close()
if __name__ == '__main__':
sql_tuple = ("127.0.0.1","root","root","123456",3306)
# 定义一个元组传入我们的数据库连接信息 并且将其传入*args魔法变量中
mysql = my_sql(*sql_tuple)
res = mysql.get_quert_sql_result('SELECT * FROM member WHERE mobile_phone="12345678900";')
#调用查询方法来查数据
print(res)
mysql.update_sql('UPDATE member SET leave_amount=1314 WHERE mobile_phone="12345678900";')
#调用更新方法来修改数据
res = mysql.get_quert_sql_result('SELECT leave_amount FROM member WHERE mobile_phone="12345678900";')
#调用查询方法来查数据 是否已经完成更新
print(res)
mysql.colse()
# 关闭连接池 释放资源
