Insert dataframes to cockroachdb table

Question

I have tried to google this and haven't found anything. Basically I need to upload a dataframe to cochroach database in a docker container. I tried this with a small test dataframe about cars at first using psycopg2, but couldn't get it to work.

import psycopg2

conn = psycopg2.connect(
       host = '172.19.0.2',
       user = 'root',
       port = 26257,
       database = 'iiwari_org'
)
conn.set_session(autocommit=True)

from pandas import DataFrame

c = conn.cursor()

c.execute('CREATE TABLE IF NOT EXISTS accounts (test INT PRIMARY KEY)')
conn.commit()

Cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
       'Price': [22000,25000,27000,35000]
       }

df = DataFrame(Cars, columns= ['Brand', 'Price'])
df.to_sql('CARS', conn, if_exists='replace', index = False)

c.execute('''  
SELECT * FROM CARS
         ''')

for row in c.fetchall():
   print (row)
   )

But it gives me error:

]




import psycopg2

conn = psycopg2.connect(
        host = '172.19.0.2',
        user = 'root',
        port = 26257,
        database = 'iiwari_org'
)
conn.set_session(autocommit=True)

…
df = DataFrame(Cars, columns= ['Brand', 'Price'])
df.to_sql('CARS', conn, if_exists='replace', index = False)
 
c.execute('''  
SELECT * FROM CARS
          ''')

for row in c.fetchall():
    print (row)
---------------------------------------------------------------------------
SyntaxError                               Traceback (most recent call last)
/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1680         try:
-> 1681             cur.execute(*args, **kwargs)
   1682             return cur

SyntaxError: at or near "?": syntax error
DETAIL:  source SQL:
SELECT name FROM sqlite_master WHERE type='table' AND name=?
                                                           ^
HINT:  try \h SELECT


The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
<ipython-input-200-89fcd218cecd> in <module>
     21 
     22 df = DataFrame(Cars, columns= ['Brand', 'Price'])
---> 23 df.to_sql('CARS', conn, if_exists='replace', index = False)
     24 
     25 c.execute('''  

/usr/local/lib/python3.8/dist-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2603         from pandas.io import sql
   2604 
-> 2605         sql.to_sql(
   2606             self,
   2607             name,

/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    587         )
    588 
--> 589     pandas_sql.to_sql(
    590         frame,
    591         name,

/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1825             dtype=dtype,
   1826         )
-> 1827         table.create()
   1828         table.insert(chunksize, method)
   1829 

/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in create(self)
    719 
    720     def create(self):
--> 721         if self.exists():
    722             if self.if_exists == "fail":
    723                 raise ValueError(f"Table '{self.name}' already exists.")

/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in exists(self)
    706 
    707     def exists(self):
--> 708         return self.pd_sql.has_table(self.name, self.schema)
    709 
    710     def sql_schema(self):

/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in has_table(self, name, schema)
   1836         query = f"SELECT name FROM sqlite_master WHERE type='table' AND name={wld};"
   1837 
-> 1838         return len(self.execute(query, [name]).fetchall()) > 0
   1839 
   1840     def get_table(self, table_name, schema=None):

/usr/local/lib/python3.8/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1691 
   1692             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 1693             raise ex from exc
   1694 
   1695     @staticmethod

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': at or near "?": syntax error
DETAIL:  source SQL:
SELECT name FROM sqlite_master WHERE type='table' AND name=?
                                                           ^
HINT:  try \h SELECT

I am using a cockroachdb docker container.

Answer 1

You appear to be using the psycopg2 python driver, but according to https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html, only the SQLAlchemy and sqlite3 drivers are supported by pandas.DataFrame.to_sql.

I was able to get this to work using SQLAlchemy using the following code:

from sqlalchemy import create_engine

conn = create_engine('cockroachdb://[email protected]:26257/iiwari_org')

from pandas import DataFrame

Cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
       'Price': [22000,25000,27000,35000]
       }

df = DataFrame(Cars, columns= ['Brand', 'Price'])
df.to_sql('CARS', conn, if_exists='replace', index = False)

CockroachDB's own docs detail how to work with SQLAlchemy here: https://www.cockroachlabs.com/docs/v20.2/build-a-python-app-with-cockroachdb-sqlalchemy.html.