python mysql操作

安装

pip install pymysql

连接数据库

import pymysql
db = pymysql.connect(host=“127.0.0.1”,#IP地址
                     user="root",#用户名
                     passwd="123456",#用户密码
                     db="test",#库名称
                     charset='utf8mb4')

创建游标对象

cursor = db.cursor()

执行SQL查询

sql = "select version()"
cursor.execute(sql)

获取单条数据

data = cursor.fetchone()

获取多条数据

cursor.fetchall()

获取字段和数据

#取各字段名称
fields = []
for column in cursor.description:
     fields.append(column[0])
#将字段和数据拼接在一起，放入到一个列表里       
fields_count = len(fields)
result_arrays = []
for record in cursor.fetchall():
	obj = dict()
    for i in range(fields_count):
    	obj[fields[i]] = record[i]
    result_arrays.append(obj)

插入数据

# SQL 插入语句
sql = "insert into employee(first_name,last_name,age,sex) values('mac','mohan',20,'男')"
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
# 关闭数据库连接
db.close()

更新数据

sql = "update employee set age = age + 1 where sex = '%s' " % ("男")
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()
db.close()

删除数据

sql = "delete from employee where age > '%s' " % ("20")
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()
db.close()