Python【pymysql】

下载安装pymyql库

pip install pymysql

字典输出数据

cur = db.cursor(pymysql.cursors.DictCursor)

连接数据库

# 导入pymysql库
import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'root'
DBNAME = 'dbtest'

try:
	# 数据库连接对象
    db= pymysql.connect(host=DBHOST,user=DBUSER, password=DBPASS, database=DBNAME)
    print('数据库连接成功!')
except pymysql.Error as e:
    print('数据库连接失败'+str(e))

查询

# 游标对象
cur = db.cursor()
# sql语句
sql = "SELECT * FROM student where id = %s"
# 值
value=("2112114333")
try:
    rows= cur.execute(sql, value)
    # 数据表格转换成元组(("2112111028","小唐"),("2112110728","小钟"),)
    data = cur.fetchall()
    # 事务提交
    db.commit()
    print('数据查询成功！')
except pymysql.Error as e:
    print("数据查询失败："+e )
    # 事务回滚
    db.rollback()

增加

# 游标对象
cur = db.cursor()
# sql语句
sql = "INSERT INTO student (id,`name`) VALUES (%s,%s)"
# 值
value=("21121145555","小陈")
try:
    rows = cur.execute(sql, value)
    # 事务提交
    db.commit()
    print('数据增加成功！')
except pymysql.Error as e:
    print("数据增加失败："+e )
    # 事务回滚
    db.rollback()

修改

# 游标对象
cur = db.cursor()
# sql语句
sql = "UPDATE student SET name =%s WHERE id = %s"
# 值
value=("小陈","21121145555")
try:
    rows = cur.execute(sql, value)
    # 事务提交
    db.commit()
    print('数据修改成功！')
except pymysql.Error as e:
    print("数据增加失败："+e )
    # 事务回滚
    db.rollback()

删除

# 游标对象
cur = db.cursor()
# sql语句
sql = "DELETE FROM student WHERE id = %s"
# 值
value=("21121145555")
try:
    rows = cur.execute(sql, value)
    # 事务提交
    db.commit()
    print('数据删除成功！')
except pymysql.Error as e:
    print("数据删除失败："+e )
    # 事务回滚
    db.rollback()