import pymysql
db = pymysql.connect(host='localhost',user='root',passwd='123456',port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
# 获取MySQL的版本号
data = cursor.fetchone()
# 创建数据库 spiders ,默认编码UTF-8
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4')
db.close()
创建数据表
import pymysql
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 创建数据表
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255)NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY(id))'
cursor.execute(sql)
db.close()
插入数据
import pymysql
id = '2019210050'
user = 'wiselisx'
age = 22
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 插入数据
sql = 'INSERT INTO students (id, name, age) values(%ss, %ss, %ss)'
try:
cursor.execute(sql,(id, user, age))
db.commit()
except:
db.close()
db.close()