python对mysql数据库的连接可由 pymysql 模块实现。
基本步骤:连接数据库、创建游标对象、读写操作、关闭游标、关闭连接。
示例:
建立一个数据库,命名python_mysqlconnect_demo,创建两张表,命名table1、table2,表结构均为
1-1.插入一条数据
向table1中插入记录:(1, 'name1')
import pymysql
# 1.连接数据库
conn = pymysql.connect(
host='localhost',
user='root',
password='xxx',
db='python_mysqlconnect_demo',
charset='utf8'
)
# 2.创建游标对象
cur = conn.cursor()
# 3.操作
try:
insert_one_sql = "insert into table1 values(1, 'name1');"
cur.execute(insert_one_sql)
except Exception as e:
print("插入数据失败:", e)
else:
conn.commit()
if(cur.rowcount > 0):
print("插入数据成功。")
# 4.关闭游标
cur.close()
# 5.关闭连接
conn.close()
1-2.插入多条数据
向table2中插入记录:(1, 'name1')至(10, 'name10')
# 3.操作
try:
insertList = []
for i in range(10):
insertList.append((i+1,"name%s"%(i+1)))
insert_multi_sql = "insert into table2 values(%s, %s);"
cur.executemany(insert_multi_sql, insertList)
except Exception as e:
print("插入多条数据失败!", e)
else:
conn.commit()
if (cur.rowcount > 0):
print("插入多条数据成功。")
2-1.删除一条数据
删除table1中id为1的记录
# 3.操作
try:
delete_one_sql = "delete from table1 where id=1;"
cur.execute(delete_one_sql)
except Exception as e:
print("删除数据失败:", e)
else:
conn.commit()
if (cur.rowcount > 0):
print("删除数据成功;")
2-2.删除表中所有数据
删除table2中的所有记录
# 3.操作
try:
delete_all_sql = "delete from table2;"
cur.execute(delete_all_sql)
except Exception as e:
print("删除数据失败:", e)
else:
conn.commit()
if (cur.rowcount > 0):
print("删除数据成功;")
3.修改一条数据
将table1中id为1记录的name值改为“name111”
# 3.操作
try:
update_sql = "update table1 set id=1,name='name111;'"
cur.execute(update_sql)
except Exception as e:
print("更新数据失败:", e)
else:
conn.commit()
if (cur.rowcount > 0):
print("更新数据成功;")
4-1.查询一条数据
查询table2中id为6的记录
# 3.操作
select_one_sql = "select * from table2 where id=6;"
cur.execute(select_one_sql)
print(cur.fetchone())
4-2.查询表中全部数据
查询table2中的所有记录
# 3.操作
select_all_sql = "select * from table2;"
cur.execute(select_all_sql)
result_tuple = cur.fetchall() # 获取所有的查询结果,类型是元组
print(result_tuple)
参考