Julia—LibPQ.jl

Suggest Edits

LibPQ.jl is a Julia wrapper for the libpq C library that can be used to connect to bit.io. It supports returning data in a variety of formats (such as Tables) that can be used in other Julia applications.

Connecting to bit.io with Julia (LibPQ.jl)

In order to connect to bit.io with Julia, you'll need to find your PosgreSQL connection credentials from the bit.io Connect Tab of the database to which you would like to connect. You can find everything you need to know about your PostgreSQL credentials here.

Install LibPQ.jl

First, in your Julia environment, install LibPQ.jl. You can do so with the Julia repl by typing ] to access the package manager and then typing add LibPQ. We will also use the Tables.jl, SQLStrings.jl, and DataFrames.jl packages for formatting our SQL strings and resulting tables.

Set Up Database Credentials

All you need to connect is your PostgreSQL connection string, which can be found in the bit.io Connect Tab. You can use the LibPQ.Connection method to establish a connection.

LibPQ.Connection(pg_string)

Using the Connection to Execute a Query

We can execute a query on a table as follows (replacing <pg_string> with your PostgreSQL connection string). We use a public database of surname frequencies from the 1990, 2000, and 2010 U.S. Censuses as an example.

using LibPQ, Tables, SQLStrings, DataFrames

query = """
SELECT *
FROM "2010_frequently_occurring_surnames"
LIMIT 10;
"""

query = sql`$query`

result = LibPQ.Connection(<pg_string>) do conn
    execute(conn, query.args[1])
end

data=columntable(result)

We can see a nicely-formatted output table using the DataFrames package.

DataFrame(data)

which yields

10×11 DataFrame
 Row │ name       rank    count    prop100k  cum_prop100k  pctwhite  pctblack  pctapi   pctaian  pct2prace  pcthispanic 
     │ String?    Int64?  Int64?   Float64?  Float64?      String?   String?   String?  String?  String?    String?     
─────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ SMITH           1  2442977    828.19        828.19  70.9      23.11     0.5      0.89     2.19       2.4
   2 │ JOHNSON         2  1932812    655.24       1483.42  58.97     34.63     0.54     0.94     2.56       2.36
   3 │ WILLIAMS        3  1625252    550.97       2034.39  45.75     47.68     0.46     0.82     2.81       2.49
   4 │ BROWN           4  1437026    487.16       2521.56  57.95     35.6      0.51     0.87     2.55       2.52
   5 │ JONES           5  1425470    483.24       3004.8   55.19     38.48     0.44     1        2.61       2.29
   6 │ GARCIA          6  1166120    395.32       3400.12  5.38      0.45      1.41     0.47     0.26       92.03
   7 │ MILLER          7  1161437    393.74       3793.86  84.11     10.76     0.54     0.66     1.77       2.17
   8 │ DAVIS           8  1116357    378.45       4172.31  62.2      31.6      0.49     0.82     2.45       2.44
   9 │ RODRIGUEZ       9  1094924    371.19       4543.5   4.75      0.54      0.57     0.18     0.18       93.77
  10 │ MARTINEZ       10  1060159    359.4        4902.9   5.28      0.49      0.6      0.51     0.22       92.91

To learn about more of the functionality of LibPQ.jl, read the official documentation.

Updated 10 months ago