Skip to content

Instantly share code, notes, and snippets.

@andrewdoss-bit
Last active August 21, 2022 07:07
Show Gist options
  • Save andrewdoss-bit/d70131ea871309c03a6d26146991669e to your computer and use it in GitHub Desktop.
Save andrewdoss-bit/d70131ea871309c03a6d26146991669e to your computer and use it in GitHub Desktop.
Upload csv to bit.io from Python w/ psycopg2
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 "<YOUR_USERNAME>/<YOUR_REPO>"."test" (
a integer,
b integer,
c integer
)
"""
with b.get_connection() as conn:
cursor = conn.cursor()
cursor.execute(create_table_sql)
# Copy csv from a local file
copy_table_sql = """
COPY "<YOUR_USERNAME>/<YOUR_REPO>"."test" FROM stdin WITH CSV HEADER DELIMITER as ',';
"""
with open('test.csv', 'r') as f:
with b.get_connection() 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment