MySQL存储
首先连接数据库然后创建一个数据库:
import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306)#声明一个MySQL连接对象
#host是数据库的公网ip
cursor = db.cursor()#调用cursor()方法获得MySQL的操作游标,可用于执行SQL语句
cursor.execute('SELECT VERSION()')#execute()方法用于执行SQL语句,这里是获得当前的版本
data = cursor.fetchone()#获取第一条数据
print('Database version:',data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")#创建一个数据库spiders并将其编码格式设置为utf-8
db.close()
然后创建数据表:
import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')#声明一个MySQL连接对象
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))'
#创建一个名为students的数据表
cursor.execute(sql)
db.close()
接着插入数据:
import pymysql
id = '201612052544'
user = 'Jone'
age = 21
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO students(id,name,age) values(%s,%s,%s)'#用格式化字符%s来表示要插入的数据
try:
cursor.execute(sql,(id,user,age))
db.commit()#执行db对象的commit()方法实现数据到插入
except:
db.rollback()#如果执行失败就执行数据回滚,相当于什么都没有发生过
db.close()
然后是查询数据:
import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:',cursor.rowcount)
one = cursor.fetchone()
print('One',one)
results = cursor.fetchall()
print('Results:',results)
print('Results Type:',type(results))
for row in results:
print(row)
except:
print('errror')
db.close()