Python学习笔记|Python连接MySQL数据库并读取显示数据数据库|python|sql|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=https://www.it610.com/article/{"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=https://www.it610.com/article/{"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 = 'INSTER 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()