MySQL数据库的连接与数据库的基本操作

一：    导入数据库并连接

import pymysql.cursors
#链接数据库
connect = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='root',
    db='sz1704',
    charset='utf8mb4'
)
# print("连接成功")

二：  

 

#获取游标
cursor = connect.cursor()

三：

 

#删除表
sql = 'DROP TABLE IF EXISTS student'
cursor.execute(sql)
connect.commit()

四： 

# 创建表
sql = 'CREATE TABLE student(id INTEGER PRIMARY KEY, name TEXT)'
try:
    cursor.execute(sql)
    connect.commit()
except:
    print('该表名已经存在')

五：

#插入数据
sql = "UPDATE student SET name = '%s' WHERE id = %d "
data = ('student2', 1)
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')

六：

#修改数据
sql = "DELETE FROM student WHERE id = %d LIMIT %d"
data = (1, 2)
cursor.execute(sql % data)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')

七：

#事务处理
sql_l = "UPDATE student SET name = name + '1' WHERE id = 1"
try:
    cursor.execute(sql_l)
except Exception as e:
    connect.rollback() #事务回滚
    print("事务处理失败", e)
else:
    connect.commit() #事务提交
    print('事务处理成功', cursor.rowcount)

八：    

#关闭链接
cursor.close()
connect.close()

九：    整体代码

import pymysql.cursors
#链接数据库
connect = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='root',
    db='sz1704',
    charset='utf8mb4'
)
# print("连接成功")

#获取游标
cursor = connect.cursor()

#删除表
sql = 'DROP TABLE IF EXISTS student'
cursor.execute(sql)
connect.commit()

# 创建表
sql = 'CREATE TABLE student(id INTEGER PRIMARY KEY, name TEXT)'
try:
    cursor.execute(sql)
    connect.commit()
except:
    print('该表名已经存在')

#插入数据
sql = "UPDATE student SET name = '%s' WHERE id = %d "
data = ('student2', 1)
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')

#修改数据
sql = "DELETE FROM student WHERE id = %d LIMIT %d"
data = (1, 2)
cursor.execute(sql % data)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')

#事务处理
sql_l = "UPDATE student SET name = name + '1' WHERE id = 1"
try:
    cursor.execute(sql_l)
except Exception as e:
    connect.rollback() #事务回滚
    print("事务处理失败", e)
else:
    connect.commit() #事务提交
    print('事务处理成功', cursor.rowcount)

#关闭链接
cursor.close()
connect.close()