python操作Mysql数据库

python操作Mysql数据库

需求：

利用python对Mysql数据库进行操作（连接，增删改查）

数据库：test
数据库中的表：user
user字段：id(主键，自增长)，name，sex，age

---

1.导入pymysql，连接数据库

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库

# 导入所需的库
import pymysql
# 连接配置
sql_con = pymysql.connect(
    host='localhost',		#主机地址，本地:localhost,远程：ip地址 
    user='root',			#用户名
    password='root',		#密码（数据库密码）
    db='test',				#数据库名称
    port=3306, 				#端口
    autocommit=False, 		#是否开启自动提交，默认关闭
    charset='utf8')			#字符编码格式

---

2.【增】插入数据到user表

2.1 普通插入

cursor = sql_con.cursor()	#创建一个游标对象cursor
# SQL 插入语句
sql = """INSERT INTO user(name, sex, age)
         VALUES ('张三', '男', 20)"""
try:
   cursor.execute(sql)		# 执行sql语句
   sql_con.commit()   		# 提交到数据库执行，如果autocommit=True则不需要手动提交 
except:
   sql_con.rollback() 		# 如果发生错误则回滚
   
cursor.close()  			# 关闭不使用的游标对象
sql_con.close() 			# 关闭数据库连接

插入结果

2.2 变量传值，动态插入

name = "李四"
sex = "男"
age = 19

# SQL 插入语句
# sql = """INSERT INTO user(name, sex, age)			
#         VALUES ('%s', '%s', %s)"""%(name,sex,age)		#直接拼接
sql = "INSERT INTO user(name, sex, age) VALUES (%s, %s, %s)"

cursor = sql_con.cursor()	#创建一个游标对象cursor
try:
   cursor.execute(sql,(name,sex,age))		# 执行sql语句,并传值拼接（直接拼接，就不需要传值）
   sql_con.commit()   		# 提交到数据库执行，如果autocommit=True则不需要手动提交 
except:
   sql_con.rollback() 		# 如果发生错误则回滚
   
cursor.close()  			# 关闭不使用的游标对象
sql_con.close() 

插入结果

---

3.【删】删除user表数据

修改sql语句 为 delete语句，其他步骤以增加数据一致

# 删除user表中，age字段大于19的数据
sql = "DELETE FROM user WHERE age > %s" % (19)

执行结果

---

4.【改】修改user表数据

修改sql语句 为 updata语句，其他步骤以增加数据一致

# 更新user表中sex=男的age为108。
sql = "UPDATE user SET age = 108 WHERE sex = '%s'" % ('男')

执行结果

---

• 【增、删、改】 除了sql语句的不同，其他操作都是一致的
• 对于【查】操作与其略有不同。

---

5.【查】查询user表数据

对于查询操作有以下两种方式推荐：

①通过游标，fetch系列方法进行操作。
②结合pandas库，对数据库进行读取，返回DataFrame格式数据

两种方法各有优劣，可自行根据具体情形，择优选择使用

数据库中有以下8条数据

5.1 通过游标，fetch系列方法查询数据

cursor = sql_con.cursor()   # 获取游标对象
sql = 'select * from user '	# 查询user表所有数据

r = cursor.execute(sql) 	# 通过游标对象，执行sql语句,返回值为受影响记录的行数
print("查询到%s条数据"% r)
print("==========fetchone()获取一条数据==========")
print(cursor.fetchone())  	# 第一条数据
print("=====再次执行fetchone()会获取第二条数据=====")
print(cursor.fetchone())  	# 第二条数据
print("========fetchmany(4)获取多条数据==========")
print(cursor.fetchmany(4))  # 指定获取4条， 此时游标已指到第三条数据，所以从第三条开始获取4条数据（游标可以理解为对获取位置的标记）
print("=========fetchall()获取全部数据===========")
print(cursor.fetchall())
print("=======再次执行fetchall()获取全部数据=======")
# 再次执行，获取到的将是一个空元组，因为上边的fetchall已经取完了，游标到了最末端无数据。
print(cursor.fetchall())  	# 当获取完毕，再查询数据返回为（）
# 关闭游标
cursor.close()
# 关闭连接
sql_con.close()

查询结果

• 由结果可知，在同一个连接查询中，游标的位置一直在随着查询往后移动。
  虽然fetchall()是获取全部数据，但获取的是当前游标及以后的全部数据
  所以通过游标方式查询数据，要注意游标所处的位置。

游标的位置是在变化的，当能我们也可以操作游标
例如，重置游标位置等操作
可以通过scroll()来操作游标

scroll(self, value, mode="relative")
value：	偏移量 大于0向后移动; 小于0向前移动
mode： 	relative:表示从当前所在的行开始移动
		absolute:表示从第一行开始移动 
		(默认relative)

举例：接上5.1，查询cursor.fetchall()为 空后，操作游标

.....接上5.1......
.................
print("=======再次执行fetchall()获取全部数据=======")
# 再次执行，获取到的将是一个空元组，因为上边的fetchall已经取完了
print(cursor.fetchall())  	# 当获取完毕，再查询数据返回为（）

print('=======操作游标，回退一位后=========')
cursor.scroll(-1)  			# 游标在当前位置（末端），向前回退一位
print(cursor.fetchall())    # 再次获取 全部数据
print('=======重置游标后=========')
cursor.scroll(0, mode="absolute")  # 初始游标位置，从0开始
print(cursor.fetchall())    # 再次获取 全部数据

cursor.close()				# 关闭游标
sql_con.close()				# 关闭连接

查询结果

5.2 结合pandas库，查询数据

Pandas 是 Python 语言的一个扩展程序库,用于数据处理分析。
使用pd.read_sql()函数可得到数据库的查询结果,
返回DataFrame格式的数据。

’‘’
# 导入库
import pandas as pd

sql = "select * from user"	#查询语句  查询test数据库中user表全部信息
df_sql = pd.read_sql(sql,sql_con) #执行sql语句并返回结果

print(df_sql)

输出查询结果：

对于查询数据，这两种方式各有优缺，可以视情况选择合适的。

---

---

谢谢阅读，感谢支持！如有错误，请多多指教.