Stata and SQLite

Stata and SQLite

For those familiar with SQL, Stata’s lack of SQL support (apart from ODBC) can be perceived as a problem. However, Stata’s integration with Python offers a solution. Here are examples with one Stata script for exporting data to an SQLite database and another one for querying an existing SQLite database. In each case, just a few lines of coding are required. First, to read a Stata data file and export the data in the form of an SQLite database, this code can be used.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* Convert a Stata datafile to an SQLite database */
 
local filename  "/home/ranstam/test.dta"
local dbname    "/home/ranstam/test.db"
local tablename "file1"
 
python:
 
import sqlite3, pandas as pd
conn = sqlite3.connect('`dbname'')
 
df = pd.read_stata('`filename'')
df.to_sql('`tablename'', conn, if_exists='replace', index=False)
 
end

In Stata, this code reads and queries an SQLite database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* Import SQLite data */
 
local dbname   "/home/ranstam/test.db"
local filename "/home/ranstam/test.dta"
local query    "select * from file1"
 
python:
 
import sqlite3, pandas as pd
conn = sqlite3.connect('`dbname'')
df = pd.read_sql('`query'', conn)
df.to_stata('`filename'') 
 
end
 
use "`filename'", clear

Liked this post? Follow this blog to get more.