还没有安装MySQL的读者可以先看这篇文章
SQL语句学起来非常简单,可以看菜鸟教程学习
本文主要讲解在python与R中连接、操作MySQL,这个过程经常要使用SQL语句,所以不熟悉SQL的读者可以先看上面的链接了解一下。
MySQL在python中的使用
在python3中使用pymysql库
在命令行中用下面命令安装
pip install PyMySQL
连接数据库并执行SQL语句
我们可以用pymysql库调用SQL语句来完成与MySQL的交互,整套流程如下
import pymysql # 用于连接并操作MySQL数据库
connection = pymysql.connect(host='localhost',
user='root',
password='', # 你安装MySQL时设置的密码
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
create_db = 'CREATE DATABASE IF NOT EXISTS dbname;'
create_table = '''create table dbname.table_name
(
title char(20),
price int,
url char(100)
);
'''
sql = 'insert into dbname.table_name(title, price, url)values(%s,%d,%s)'
try:
with connection.cursor() as cursor:
cursor.execute(create_db)
cursor.execute(create_table)
for item in items: # item是存储三个字段的字典
cursor.execute(sql, (
item['title'], item['price'], item['url']))
connection.commit() # 提交SQL操作,使之生效
finally:
connection.close()
上面我们展示的是创建数据库、表,插入数据的过程,这些操作都是通过执行SQL语句的,所以只要学好SQL语句,写成字符串的形式,再放到cursor.execute里面执行,套用上面这个链接模板即可。
数据库存在与否的问题
第一,操作MySQL需要先有这个数据库和表,表要具体指定字段和类型。你可以先在MySQL客户端手动创建好,也可以像上面那样用命令来创建,不要想像使用MongoDB那样调用时自动创建。因为这是关系型数据库,限制比较多,存储时每一列类型要是一样的,不像json格式随便存,因此需要严格创建。
第二,如果像上面这样创建数据库与表,要考虑这个数据库和表是否已经存在。当直接CREATE DATABASE dbname,这时如果数据库已经存在则会报错,所以我们加了一个判断CREATE DATABASE IF NOT EXISTS dbname;,创建表也是一样。
第三,如果你是手动在客户端创建了一个数据库和表,再用python进行的连接,则可以这样
connection = pymysql.connect(host='localhost',
user='root',
db = 'dbname',
password='', # 你安装MySQL时设置的密码
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
在初始化连接时就指定数据库,之后的操作就只要用表名而不需要前面加数据库的名字了,比如
sql = 'insert into dbname.table_name
改成
sql = 'insert into table_name
再强调一下,这样用必须是确定MySQL中有这个数据库了,才可以这样一开始就连接它。
使用pymysql提取数据
直接上代码
import pymysql
connection = pymysql.connect(host='localhost',
user='root',
db = 'dbname',
password='', # 你安装MySQL时设置的密码
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
sql = 'select * from table_name'
try:
with connection.cursor() as cursor:
cursor.execute(sql)
a = cursor.fetchmany(3)
finally:
connection.close()
print(a)
上面代码的流程是连接数据库,执行select所有数据的SQL语句
用fetchmany函数提取3行数据,存储到变量a中
变量a即使一个有三个元素的列表,每个列表以字典形式存储,可以用于进一步处理
其中fetchmany(3)可以换成fetchone()和fetchall()分别表示提取一条和全部提取。
使用pandas提取
import pymysql
import pandas as pd
connection = pymysql.connect(host='localhost',
user='root',
db = 'ganji2',
password='mysqlmima', # 你安装MySQL时设置的密码
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
sql = 'select * from dog'
a = pd.read_sql(sql, con = connection)
print(a)
这个a就直接是DataFrame了
MySQL在R中的使用
在R语言中使用RMySQL包
直接用下面这条命令安装
install.packages("RMySQL")
连接数据库和提取数据
library(RMySQL)
con <- dbConnect(MySQL(), user="root", password="", # 传入安装MySQL时设置的密码
dbname="dbname", host="localhost")
# 提取10条数据
rs <- dbSendQuery(con, "select * from table_name limit 10;")
data <- fetch(rs, n = 5) # 只提取5条
data # 即数据框
dbDisconnect(con) # 断开连接
读取数据也可以不同fetch,而是简单地这样
dbReadTable(con, 'table_name')
得到的结果就是整个数据框
写入数据
# 连接之后
dbWriteTable(con, 'mtcars', mtcars)
# 再关闭数据库
其中con是连接对象,'mtcars'是新创建的表名,mtcars是要写入的数据框。与python不同,R语言中不需要事先建表再写入,而是写入时自动建表。
但是当MySQL中已经有了名为mtcars的表,再运行这条命令就会报错。如果想在一个表中添加数据,需要用dbSendQuery调用SQL语句。
专栏信息
专栏目录:目录