9 月初,我对 python 爬虫
燃起兴趣,但爬取到的数据多通道实时同步读写用文件
并不方便,于是开始用起mysql
。这篇笔记,我将整理近一个月的实战中最常用到的 mysql
语句,同时也将涉及到如何在python3
中与 mysql
实现数据交换。
关于工具/库,特别说明下:
1、我安装了 mysql
,并直接采用管理员身份运行命令行提示符(cmd)
查看 mysql
,并没有安装任何 mysql
的可视化图形界面工具。
2、在 python
脚本中,我采用 pymysql
和 sqlalchemy
这两个库与 mysql
建立连接,用 pandas
来处理数据。
一、建立连接与数据交互
与 mysql 交互的方式,我目前共使用 4 种。其中采用管理员身份运行命令行提示符(cmd)
查看 mysql
,其操作图示可另写一篇。这里就不占篇幅了。mysql的可视化图形界面工具,我目前并没有用到,也没有迫切使用它的需要。另外 3 种方式都是通过 python 脚本进行。
情境A:python 演算得出数据,想要写入数据库
python 脚本已得到表格类大量数据,想要一次性写入数据库,常用代码如下:
import pandas as pd
# 与 mysql 建立连接
from sqlalchemy import create_engine
conn_eng = create_engine('mysql+pymysql://username:password@localhost:3306/databasename',encoding='utf8')
# 调用 pandas 的方法,数据写入mysql
pd.io.sql.to_sql(your_df, "table_name", conn_eng, if_exists='append',index=False)
表格类数据,我用的是 pandas
的 dataframe
结构。pd.io.sql.to_sql()
的参数还有许多其它用途,但上面这种是我个人使用最高频的。效果是:无需自己提前建表,将自动建新表。美中不足是:表的列属性自动生成,通常不合心意,还需检查和修改。
如果不想用 pd.io.sql.to_sql()
或者想更精细、复杂的操作,则用到下面的情境C。
情境B:python 脚本想从 mysql 拿到数据
如果已经存在某个表格,想要向该表格提交某条指令,需返回数据,我用的是 pandas
的read_sql ()
,返回的数据类型是 pandas
的 dataframe
。sql 查询语句挺好写的,具体总结在本文下方。
import pymysql
# 与 mysql 建立连接
conn = pymysql.connect('localhost','username','password','databasename')
# sql 语句定义为一个字符串
sql_search = 'select question_id from topic_monitor where is_title=0 ;'
# 调用 pandas 的 read_sql() 方法拿到 dataframe 结构的数据
question_ids = pd.read_sql(sql_search,conn)
# 关闭连接
conn.close()
情境C:python 脚本单方面向 mysql 发出指令,无需拿到数据
如果已经存在某个表格,想要向该表格提交某条指令而无需返回数据时,比如:建表、对数据的增改删、对列的名称、列的属性修改等,代码如下。
import pymysql
# 与 mysql 建立连接
conn = pymysql.connect('localhost','username','password','databasename')
cursor = conn.cursor()
# sql 语句定义为一个字符串,插入一行数据
sql_insert = 'INSERT INTO questions(q_id,q_title,q_description,q_keywords,q_people,q_pageview,time) VALUES( "'\
+ str(quesition_id) + '", "' + str(one[0])+ '", "' + str(one[1]) + '", "' + str(one[2]) + '", "' \
+ str(one[3]) + '", "' + str(one[4]) + '", "' + str(datetime.datetime.now()) + '");'
# sql 语句定义为一个字符串,修改某个数据(另一个表格)
sql_update = 'update topic_monitor SET is_title="1" where question_id = "' + str(quesition_id) + '";'
# 提交指令
cursor.execute(sql_insert)
cursor.execute(sql_update)
conn.commit()
# 插入一行数据;仅当该数据与表格已有数据不重复时才插入,否则就不会插入
sql_insert = 'INSERT INTO `topic_monitor`(question_id,is_title,q_type,topic_id,time) SELECT "'\
+ x[0] + '", "0", "0","' + str(topic_id) + '", "'+ str(now) + '" FROM DUAL WHERE NOT EXISTS(\
SELECT question_id FROM topic_monitor WHERE question_id = "' + x[0] + '")'
cursor.execute(sql_insert)
conn.commit()
# 关闭连接
cursor.close()
conn.close()
通过上面几种实用情况可以看到,python
与 mysql
实现交互的过程,通常分为:建立连接、把sql语句定义为字符串,提交指令、关闭连接。核心的技能在于 sql语句;除了定义sql语句字符串,其余3个处理都是固定的写法。
我在最初一个月的实践中,最常出现的错误有:
- 值的引用没有加上引号;
- 符号错乱:多一个符号,少一个符号;
- 值的类型不符合:不管 mysql 表格中该值是数,还是文本,在定义 sql 语句的字符串时,对每个值都需要转化为字符串;
- 拷贝自己的代码时,忘记修改databasename。
二、sql语句:搜索查询
搜索是指在数据库的某个表格中查询符合特定条件的数据,并返回查询结果。其基本结构为:
SELECT 【范围】FROM table_name 【条件】;
其中,范围是必须指定的,而条件可有可无。
变量A:范围,是指返回查询结果的范围。
返回该表格的所有字段,用 * 表达:
SELECT * FROM table_name ;
仅返回该表格的某个字段:
SELECT column_name FROM table_name ;
仅返回该表格的多个字段:
SELECT column_name_1,colu