pip install pymysql
先看个简单的
import pymysql
# 建立数据库连接
connection_sql = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='root',
database='5_10',
charset='utf8'
)
# 获取游标
cursor = connection_sql.cursor(pymysql.cursors.DictCursor)
str1 = '1'
str2 = '18'
# sql = 'select * from staff where id> %s and age>%s'
# sql1 = 'select * from staff where id= {}'.format(str1)
sql1 = 'select * from staff where id= %s'
try:
# cursor.execute(sql, [str1, str2])
# cursor.execute(sql1)
cursor.execute(sql1,str1) # 执行
a = cursor.fetchall()
print(a)
connection_sql.commit()
except Exception as e:
connection_sql.rollback() # 失败回滚
raise e
# 关闭游标
cursor.close()
# 关闭连接
connection_sql.close()
封装后的查询和非查询
host='127.0.0.1'(数据库地址,这里用的是本地)
user='root'(用户名)
password='root'(密码)
database=数据库名称
port=3306(连接端口号)
DbHandle = DataBaseHandle(host, user, passowrd, database, port) 创建对象之后就可以调用了
# 一个做查询,一个做非查询
import pymysql
# username : adil
# password : helloyyj
class DataBaseHandle(object):
''' 定义一个 MySQL 操作类'''
def __init__(self, host, username, password, database, port):
'''初始化数据库信息并创建数据库连接'''
# 下面的赋值其实可以省略,connect 时 直接使用形参即可
self.host = host
self.username = username
self.password = password
self.database = database
self.port = port
self.db = pymysql.connect(
host=self.host,
user=self.username,
password=self.password,
db=self.database,
port=self.port,
charset="utf8"
)
self.cursor = self.db.cursor(pymysql.cursors.DictCursor)
def select_info(self, sql):
try:
self.cursor.execute(sql)
data = self.cursor.fetchall()
return data
except:
print('Error: unable to fecth data')
finally:
self.cursor.close()
def no_select_info(self, sql):
try:
result = self.cursor.execute(sql)
print(result)
self.db.commit()
return result
except Exception as e:
self.db.rollback()
raise e
finally:
self.cursor.close()
if __name__ == '__main__':
DbHandle = DataBaseHandle('127.0.0.1', 'root', 'root', '5_10', 3306)
DbHandle.no_select_info("insert into stu (name,age) values ('随便起名1',19)")
DbHandle.select_info("select * from students where id < 10")