本文最后更新于 244 天前,其中的信息可能已经有所发展或是发生改变。
1.连接的基本语法如下:
pyimport pymysql
#创建连接
conn = pymysql.Connect(host="127.0.0.1", port=3306, user="root", passwd="123456",db="test")
# 创建游标
cursor = conn.cursor()
#SQL语句增
sql1 = "insert into user(name) values ('大猪')"
#SQL语句查
sql2 = "select * from user"
#SQL语句改
sql3 = "update user set name = '大大猪' where id = 3"
#SQL语句删
sql4 = "delete from user where id = 3"
# 读取数据
number = cursor.execute(sql4)
print(number)
# 提交事务
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
2.创建连接模块
# -*- coding: UTF-8 -*-
import pymysql
class Mysql(object):
def __init__(self):
#配置mysql数据库信息
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': '123456',
'db': 'demo',
'charset': 'utf8'
}
self.host = config['host']
self.username = config['user']
self.password = config['passwd']
self.port = config['port']
self.db = config['db']
self.con = None
self.cursor = None
try:
self.conn = pymysql.connect(**config)
# 所有的查询,都在连接 con 的一个模块 cursor 上面运行的
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
except:
print("DataBase connect error,please check the db config.")
def exeCommit(self, sql=''):
"""执行数据库sql语句,针对更新,删除,事务等操作失败时回滚
"""
try:
execute = self.cursor.execute(sql)
self.conn.commit()
#返回值为受影响的行数
return execute
except pymysql.Error as e:
self.conn.rollback()
error = 'MySQL execute failed! ERROR (%s): %s' % (e.args[0], e.args[1])
print("error:", error)
return error
def __del__(self):
# 关闭cursor对象
self.cursor.close()
self.conn.close()