爬虫文件存储-1:mysql
2024-08-27 18:24:52
1.连接并创建数据库
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")
db.close()
执行结果:
Database version: ('5.7.23',)
结果是创建一个名为spiders的数据库
2.创建数据表
注意:这次连接数据库时需要指定创建数据表所在的数据库,也就是db参数的值
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', 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()
3.插入一条数据
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='spiders')
cursor = db.cursor() id = ''
user = 'Bob'
age = 20 sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
try:
cursor.execute(sql, (id, user, age))
db.commit()
except:
db.rollback()
db.close()
通用写法:
这样一来,若是写入更多值的话直接给data字典增加数据就行了,就不用其他sql语句了,推荐使用
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='spiders')
cursor = db.cursor() table = 'students' data = {
'id': '',
'name': 'Bob11',
'age': 200
} keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data)) 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('Failed')
db.rollback()
db.close()
4.更新数据
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='spiders')
cursor = db.cursor() sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
db.close()
通用写法:
根据主键进行判断,若主键存在则更新,若不存在则插入,推荐使用
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='spiders')
cursor = db.cursor() table = 'students' data = {
'id': '',
'name': 'Bob',
'age': 21
} keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data)) sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys,
values=values)
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
if cursor.execute(sql, tuple(data.values()) * 2):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()
5.删除数据
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='spiders')
cursor = db.cursor() table = 'students'
condition = 'age > 20' sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback() db.close()
6.查询数据
import pymysql db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='spiders')
cursor = db.cursor() sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount)
row = cursor.fetchone()
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')
最新文章
- 【Telnet】使用Telnet协议连接到远程Shell执行脚本
- Android--菜单详解
- 正确理解Spring AOP中的Around advice
- ASP 发送邮件
- 设置xx-net,访问youtube等国外网站
- HDU2829 Lawrence(斜率优化dp)
- sql批量删除wordpress所有日志修订revision
- sql server Convert 的函数的用法 转换成浮点数
- css中的那些布局
- 玩转SSH(四):Struts + Spring + MyBatis
- [1] [转]软件架构之三层架构和MVC的关系
- 小白学Maven第二篇配置Ecilpse
- codeforces_Codeforces Round #541 (Div. 2)_abc
- 让你爱不释手的 Python 模块
- git和github的第一次接触
- eleTree树形插件引入
- gunicorn flask nginx环境配置
- VUE + vue-cli + webpack 创建新项目
- JavaWeb学习 (二十四)————Filter(过滤器)常见应用
- 画时序图工具TimingDesigner 9.2 安装指导
热门文章
- 5分钟APIG实战: 使用Rust语言快速构建API能力开放
- Linux下的画图软件
- LeetCode总结--二分查找篇
- jsp注释前台不可见
- 什么是IaaS,PaaS和SaaS及其区别
- hibernate基础学习---hierbnate2级缓存
- cmd执行Java程序
- 【题解】晋升者计数 Promotion Counting [USACO 17 JAN] [P3605]
- 题解报告:hdu 2516 取石子游戏(斐波那契博弈)
- Android内存管理(9)*MAT:Heap Dump,Shallow Heap,Retained Heap,Dominating Tree,GC Roots等的含义