Python连接MySQL数据库并读取显示数据

1.创建数据库

# 创建名为 Test 的数据库
db = pymysql.connect(host='localhost', user='root', passwd='your password', port=3306)
cursor = db.cursor()  # 创建游标
cursor.execute("CREATE DATABASE Test DEFAULT CHARACTER SET utf8")
db.close()

2.连接数据库，创建表

# 创建名为 mydata 的数据表
db = pymysql.connect(host='localhost', user='root', passwd='your password', port=3306, db='Test')
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS mydata (id VARCHAR(255) NOt NULL,name VARCHAR(255) NOT NULL ,age VARCHAR (255) NOT NULL)'
cursor.execute(sql) # 利用游标执行mysql语句
db.close()

3.向数据库中插入数据

• 动态插入数据：

data={"id":01,"name":Jack,"age":12}  # 以字典数据为例
db = pymysql.connect(host='localhost', user='root', passwd='your password', port=3306, db='Test', autocommit=True)
cursor = db.cursor()
table = 'mydata'  # 传递 '表'
keys = ','.join(data.keys())  # 字段名 data={"key":value}
values = ','.join(['%s'] * len(data))  # 构造占位符  data为你的数据,下面会生成三个占位符(%s)
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
   if cursor.execute(sql, tuple(data.values())):   # 注意变为 "元组" 进行传递
       print("successful！")
       db.commit()
except:
   print('数据插入失败！')
   db.rollback()
db.close()

• 直接构造插入：

db = pymysql.connect(host='localhost', user='root', passwd='your password', port=3306, db='Test',autocommit =True)
cursor = db.cursor()
sql = 'INSERT INTO mywifi(id,name,age) values(%s,%s,%s)' % (id, name, age)
cursor.execute(sql, (id,name,age))
# 考虑用 try....except 更好
db.commit()
db.close()
print("数据插入成功")

4.从数据库中读取并显示数据

db = pymysql.connect(host='localhost', user='root', passwd='your password', port=3306, db='Test', autocommit=True)
cursor = db.cursor()
sql = "SELECT * FROM mydata"
cursor.execute(sql)
mydata = cursor.fetchall() # 获取全部数据
# for data in mydata:
#     print(list(data))
# db.close()
pdate = pd.read_sql(sql, db)  # 以DataFrame格式读取显示
print(pdate)
db.close()