Python—psycopg2 and the bit.io Python SDK

Below is an example of how to connect to bit.io using our python library via psycopg2. This library supports generation of pre-configured psycopg2 connections to bit.io

The snippet below shows how to upload a csv file to a new or existing table using a psycopg2 connection generated using the bitdotio Python library.

import bitdotio
import pandas as pd

# This is to provide a reproducible csv,
# you can ignore and use your own csv
df_test = pd.DataFrame(
    data=[[0, 1, 2], [3, 4, 5]],
    columns=['a', 'b', 'c'])
df_test.to_csv('test.csv', index=False)

# Connect with bit.io API key credentials
b = bitdotio.bitdotio("<YOUR_API_KEY>")

# Create table, if it does not already exist
create_table_sql = """
    CREATE TABLE test (
      a integer,
      b integer,
      c integer
    )
    """

with b.get_connection("<YOUR_DATABASE_NAME>") as conn:
    cursor = conn.cursor()
    cursor.execute(create_table_sql)
    
# Copy csv from a local file
copy_table_sql = """
    COPY test FROM stdin WITH CSV HEADER DELIMITER as ',';
    """

with open('test.csv', 'r') as f:
    with b.get_connection("<YOUR_DATABASE_NAME>") as conn:
        cursor = conn.cursor()
        cursor.copy_expert(sql=copy_table_sql, file=f)
        
# Note: you can also create a new repo from Python using the API, if needed

View in Github and download .py file here.

You can also create a connection pool directly using psycopg2, as shown here. This may be easier to work with if you are experiencing disconnects due to bit.io's (currently) 60 second idle connection timeout.

import time

from psycopg2.extensions import connection
from psycopg2.pool import SimpleConnectionPool


conn_string = '<your-connection-string>'
min_conn = 0
max_conn = 100

if __name__ == '__main__':
    # instantiate a connection pool
    pool = SimpleConnectionPool(min_conn, max_conn, conn_string)

    # fetch a connection from the pool. if no existing live connection exists,
    # then a new one will be instantiated.
    conn: connection = pool.getconn()
    # run some query using the connection
    with conn.cursor() as cur:
        cur.execute('select 1;')
        print(cur.fetchall())

    # return the connection to the pool to free it up for reuse. not that we
    # could also add the keywork argument close=True here if we wanted to close
    # the connection as soon as it is returned from the pool.
    pool.putconn(conn)

    # wait for bit.io to close the connection we created initially.
    print('sleeping...')
    time.sleep(61)
    print('waking up...')

    # get a new connection from the connection pool and execute a query.
    conn = pool.getconn()
    with conn.cursor() as cur:
        cur.execute('select 1;')
        print(cur.fetchall())

    pool.putconn(conn)