python操作mysql数据库入门级示例源码(增删改查)

【实例简介】

首先在mysql中创建chat数据库，

然后确保本地环境安装了 pymysql 【管理员模式>>命令行工具>>pip3 install PyMySQL】

最后确保连接串与你本地一致，运行即可看到如下图：

【实例截图】

【核心代码】

import pymysql

#  创建连接
conn = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    passwd= 'root',
    db   = 'chat',
    charset = 'utf8'
)
#  实例化游标
cur = conn.cursor()

sql_dict = {
    'show':'show databases',
    'creat':'create table stu (id INT PRIMARY KEY ,name CHAR (10),addr CHAR (15));',
    'insert':"insert into stu(id,name,addr) VALUE (1,'XXX','XXXXX'),(2,'XXX','XXXXX');",
    'select':'select * from stu;',
    'update':"update stu set addr='  ' where id=2;",
    'delete':"delete from stu where id=1;",
    'drop':'drop table stu;'
}

#  执行SQL语句
cur.execute(sql_dict['show'])
#  获取所有数据
datas = cur.fetchall()
for db_info in datas:
     print(db_info)
#  创建表格
try:
     cur.execute(sql_dict['creat'])
     conn.commit()
     print("创建成功!")
except:
     print("表格已存在!")
#  插入数据
try:
     cur.execute(sql_dict['insert'])
     conn.commit()
     print("添加成功!")
except:
     print("数据已存在!")
     conn.rollback()  #  回滚
#  更新数据
cur.execute(sql_dict['update'])
conn.commit()
#  删除数据
try:
     cur.execute(sql_dict['delete'])
     conn.commit()
     print("删除成功!")
except:
     conn.rollback()
     print("没有id为1的数据！")
# #  查询所有数据
cur.execute(sql_dict['select'])
# #  获取所有数据
dbs = cur.fetchall()
for db_info in dbs:
     print(db_info)
#  获取一条数据
db = cur.fetchone()
print(db)
#  获取多条数据
dbs = cur.fetchmany(2)
for db_info in dbs:
     print(db_info)
#  关闭游标
cur.close()