彻底解决了 Stata 与 MySQL 数据交换的问题

彻底解决了 Stata 与 MySQL 数据交换的问题

昨天晚上睡前就在思考这个问题,之前那个 Stata 调用 R 再调用 MySQL 的方法并不是非常好用。最近看了关于 MySQL 备份和恢复的部分,知道了 MySQL 可以通过一些命令行工具进行备份和恢复。于是就诞生了三个 Stata 命令,分别用于解决 Stata 读取、存储和传递 MySQL 代码的问题。

MySQL 实用工具

MySQL 提供了很多实用的命令行工具,这里我实用了 3 个。第一个是 mysqldump,用于导出数据库中的数据到文件中;第二个是 mysqlimport,用于把文本文件导入数据库中;第三个是 mysql 可以直接在终端执行某个 sql 文件。

在 Stata 中读取 MySQL 中的数据

这部分用到的是 mysqldump 命令,思路就是先调用这个命令把需要读取的数据导出为 sql 文件(因为直接读出为 txt 文件在我的电脑上会遇到权限问题,就很烦人),然后简单处理一下再读入 Stata 即可,我把这个过程封装为一个命令:mysqli.ado,具体代码如下(需要注意这个代码需要结合你的电脑的实际情况进行修改):

Stata
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
*! mysqli:在Stata中读取MySQL中的数据
*! 程振兴 2018年9月8日
*! 【用法】
*! mysqli database_name table_name[, varname(name1, name2, ··· , namen)]
*! database_name: 数据库的名称
*! table_name: 数据表的名称
*! varname: 可选项,用于指定读取Stata后的变量名称
*! 【例如】
*! mysqli db_school tb_student, v(学号 姓名 性别 出生日期 籍贯 民族 班级)
*! mysqli 基础数据 股票列表, v(交易代码 股票代码 公司名称 挂牌日期)
*! mysqli 基础数据 上交所交易日历, v(交易所 交易日期 是否开盘)
cap prog drop mysqli
prog def mysqli
version 14.0
syntax anything[, Varname(string)]
qui{
!/usr/local/mysql/bin/mysqldump -u root -p12345 `anything' | tr "(" "\n" > temp.txt
infix strL v 1-20000 using temp.txt, clear
keep if index(v, "),") | index(v, ");")
drop if index(v, "\`")
replace v = subinstr(v, `"'"', "", .)
replace v = subinstr(v, `"),"', "", .)
replace v = subinstr(v, `");"', "", .)
split v, parse(,)
loca varnum = r(nvars)
drop v
if "`varname'" != ""{
tokenize `varname'
forval i = 1/`varnum'{
ren v`i' ``i''
}
}
erase temp.txt
}
end

例如,我的数据库里面有这么一个数据:

运行下面的 Stata 命令就可以把它读入 Stata 中:

Stata
1
mysqli 基础数据 股票列表, v(交易代码 股票代码 公司名称 挂牌日期)

把 Stata 的数据存入 MySQL 数据库

这里用到了 mysqlimport 命令,思路是将 Stata 数据导出为 txt 文件再使用这个命令读入数据库即可。需要注意,要把数据中的空值替换成\N。同时需要在终端运行下面的命令收回 mysql 对 sql 文件夹的(在前面的文章中有说,这个文件夹是我授予 mysql 存储权限的文件夹)的权限,要不然 Stata 无法将数据存入这个文件夹(即使收回权限,mysqlimport 也是可以将数据存入数据库的):

Shell
1
sudo chown mr.cheng ~/sql

然后我把这部分代码封装成了一个mysqlj.ado。这个名字的来源是因为那个叫mysqli.ado。。。为什么那个要叫mysqli.ado呢,因为之前的那个 Stata 调用 R 再调用 MySQL 封装的叫mysql.ado,所以这里想improved一下。

Stata
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*! mysqlj:将Stata中的数据存入MySQL中
*! 程振兴 2018年9月8日
*! 【用法】
*! mysqli database_name table_name
*! database_name: 数据库的名称
*! table_name: 数据表的名称
*! 【注意】:需要提前数据库中建立好对应的表格,而且数据库和表的名称中不能含有中文!
*! 【例如】
*! cnstock2
*! mysqlj db_school test
cap prog drop mysqlj
prog def mysqlj
version 14.0
syntax anything
qui{
tokenize `anything'
tostring _all, replace
foreach i of varlist _all{
cap replace `i' = "\N" if `i' == "."
}
export delimited using "/Users/mr.cheng/sql/`2'.txt", delimiter(tab) novarnames replace
!/usr/local/mysql/bin/mysqlimport -u root -p12345 --replace `1' /Users/mr.cheng/sql/`2'.txt
erase /Users/mr.cheng/sql/`2'.txt
}
end

例如,我用 cnstock2 命令下载一份股票数据:

Stata
1
cnstock2

运行下面的命令即可存入数据库:

Stata
1
mysqlj db_school test

需要注意,这里不能使用中文作为数据库的名称和表的名称,因为会产生乱码而导致找不到数据库。而且这个表必须要提前创建好。例如,这里的这个表的创建代码为:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
drop table if exists db_school.test;
create table db_school.test
(
number int,
code char(6) primary key,
name varchar(15),
total_stock_num float,
outstanding_stock_num float,
outstanding_stock_value float,
registered_capital float,
pe_ratio float,
industry varchar(40),
concept varchar(20),
area varchar(20),
close float
)engine = InnoDB;

在 Stata 中执行 MySQL 代码

这一部分和之前的 pycall 一样了,就是先写个 sql 文件,然后用 mysql 执行。我把这个过程封装成了一个命令——mysqlcall.ado

Stata
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
*! mysqlcall: 在Stata中运行MySQL代码
*! 程振兴 2018年9月8日
*! 该命令的实现原理是将MySQL代码保存为一个临时的sql文件,
*! 然后将该文件使用命令行的mysql运行,然后将结果返回。
*! 一段示例的原始代码如下:
*! ###################### !*
*! ### 原理 ### !*
*! ###################### !*
*! file open myfile using temp.sql, write replace
*! file write myfile "drop table if exists db_school.test;" _n
*! file write myfile "create table db_school.test" _n
*! file write myfile "(" _n
*! file write myfile " number int," _n
*! file write myfile " code char(6) primary key," _n
*! file write myfile " name varchar(15)," _n
*! file write myfile " total_stock_num float," _n
*! file write myfile " outstanding_stock_num float," _n
*! file write myfile " outstanding_stock_value float," _n
*! file write myfile " registered_capital float," _n
*! file write myfile " pe_ratio float," _n
*! file write myfile " industry varchar(40)," _n
*! file write myfile " concept varchar(20)," _n
*! file write myfile " area varchar(20)," _n
*! file write myfile " close float" _n
*! file write myfile ")engine = InnoDB;" _n
*! file close myfile
*! !/usr/local/mysql/bin/mysql -u root -p12345 db_school < temp.sql
*! ###################### !*
*! ### 示例 ### !*
*! ###################### !*
*! mysqlcall, s
*! mysqlcall "drop table if exists db_school.test;"
*! mysqlcall "create table db_school.test"
*! mysqlcall "("
*! mysqlcall " number int,"
*! mysqlcall " code char(6) primary key,"
*! mysqlcall " name varchar(15),"
*! mysqlcall " total_stock_num float,"
*! mysqlcall " outstanding_stock_num float,"
*! mysqlcall " outstanding_stock_value float,"
*! mysqlcall " registered_capital float,"
*! mysqlcall " pe_ratio float,"
*! mysqlcall " industry varchar(40),"
*! mysqlcall " concept varchar(20),"
*! mysqlcall " area varchar(20),"
*! mysqlcall " close float"
*! mysqlcall ")engine = InnoDB;"
*! mysqlcall, e r
cap prog drop mysqlcall
prog def mysqlcall
version 14.0
syntax [anything(name = sqlcode)] [, Start End Run]
if "`start'" != ""{
cap file close myfile
file open myfile using tempsqlfile.sql, write replace
}
if "`end'" != ""{
file close myfile
}
else{
file write myfile `sqlcode' _n
}
if "`run'" != ""{
!/usr/local/mysql/bin/mysql -u root -p12345 < tempsqlfile.sql
erase tempsqlfile.sql
}
end

例如,通过下面的工作流程就能实现创建表格、存储数据了:

Stata
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/* 创建表格 */
mysqlcall, s
mysqlcall "drop table if exists db_school.test;"
mysqlcall "create table db_school.test"
mysqlcall "("
mysqlcall " number int,"
mysqlcall " code char(6) primary key,"
mysqlcall " name varchar(15),"
mysqlcall " total_stock_num float,"
mysqlcall " outstanding_stock_num float,"
mysqlcall " outstanding_stock_value float,"
mysqlcall " registered_capital float,"
mysqlcall " pe_ratio float,"
mysqlcall " industry varchar(40),"
mysqlcall " concept varchar(20),"
mysqlcall " area varchar(20),"
mysqlcall " close float"
mysqlcall ")engine = InnoDB;"
mysqlcall, e r

/* 下载、存储数据 */
cnstock2
mysqlj db_school test

好了👌,我要把三个 do 文件放进我的系统文件夹了。

Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×