CODEWITHSUNDEEP
pythonpostgresqlpandas
Daniel
Daniel

Reputation: 5381

update pandas to postgres

I'm trying to upload a pandas dataframe to a PostgreSQL database but I'm encountering an error.

import pandas as pd
import psycopg2
import pandas.io.sql as sql

conn_string = "host='localhost' dbname='**' user='postgres' password='**' port=5432"
conn = psycopg2.connect(conn_string)
cursor = conn.cursor() 

hh = pd.read_csv("C:/opus/data/mrcog/inputs/synthpop/synth_hhlds.csv")
hh.to_sql('buildings_updated', conn)

but when i try to upload the table, I'm getting an error that i dont understand what is telling. 

DatabaseError                             Traceback (most recent call last)
<ipython-input-12-b1b2758437b2> in <module>()
     16 
     17 hh = pd.read_csv("C:/opus/data/mrcog/inputs/synthpop/synth_hhlds.csv")
---> 18 hh.to_sql('buildings_updated', conn)

C:\Anaconda2\lib\site-packages\pandas\core\generic.pyc in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
   1343         sql.to_sql(self, name, con, flavor=flavor, schema=schema,
   1344                    if_exists=if_exists, index=index, index_label=index_label,
-> 1345                    chunksize=chunksize, dtype=dtype)
   1346 
   1347     def to_pickle(self, path, compression='infer'):

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
    469     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    470                       index_label=index_label, schema=schema,
--> 471                       chunksize=chunksize, dtype=dtype)
    472 
    473 

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
   1503                             if_exists=if_exists, index_label=index_label,
   1504                             dtype=dtype)
-> 1505         table.create()
   1506         table.insert(chunksize)
   1507 

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in create(self)
    584 
    585     def create(self):
--> 586         if self.exists():
    587             if self.if_exists == 'fail':
    588                 raise ValueError("Table '%s' already exists." % self.name)

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in exists(self)
    572 
    573     def exists(self):
--> 574         return self.pd_sql.has_table(self.name, self.schema)
    575 
    576     def sql_schema(self):

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in has_table(self, name, schema)
   1515                  "WHERE type='table' AND name=%s;") % wld
   1516 
-> 1517         return len(self.execute(query, [name, ]).fetchall()) > 0
   1518 
   1519     def get_table(self, table_name, schema=None):

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in execute(self, *args, **kwargs)
   1414             ex = DatabaseError(
   1415                 "Execution failed on sql '%s': %s" % (args[0], exc))
-> 1416             raise_with_traceback(ex)
   1417 
   1418     @staticmethod

C:\Anaconda2\lib\site-packages\pandas\io\sql.pyc in execute(self, *args, **kwargs)
   1402                 cur.execute(*args, **kwargs)
   1403             else:
-> 1404                 cur.execute(*args)
   1405             return cur
   1406         except Exception as exc:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': relation "sqlite_master" does not exist
LINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...

How is it possible to fix this error? It should be a straight forward to upload the dataframe. I'm using pandas version 4.3.34.

Upvotes: 2

Views: 1897

Answers (1)

MrE
MrE

Reputation: 20768

the docs says:

flavor : ‘sqlite’, default None

Deprecated since version 0.19.0: ‘sqlite’ is the only supported option if SQLAlchemy is not used.

http://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.to_sql.html

So it seems like you should be using SQLAlchemy for this, unless your error is about the table existing already, but it seems more likely it is because you are not using SQLLite or SQLAlchemy to proxy to a real DB like Postgres.

another part of the doc:

if_exists : {‘fail’, ‘replace’, ‘append’}, default ‘fail’

fail: If table exists, do nothing. replace: If table exists, drop it, recreate it, and insert data. append: If table exists, insert data. Create if does not exist.

Upvotes: 1

Related Questions