Execution failed on sql : insert dataframe to postgresql server

Question

I'm trying to connect postgresql server and insert some dataframe to a table and this is my code

import datetime
import gzip
import logging
import os
import sys
import traceback
import numpy as np
import pandas as pd
import psycopg2
from pandas.io.sql import SQLTable, pandasSQL_builder
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from sqlalchemy import create_engine

c = psycopg2.connect(database="dc_integration", user="mastername", 
host="integrationdb.c46zksctipg7.ap-southeast-1.rds.amazonaws.com", 
password="xxx")

d = {'project_key': [1, 2], 'project_name': [3, 4], 'update_date': 
['2016-06-22 19:10:28','2016-06-22 19:10:15']}
df = pd.DataFrame(data=d)
df.to_sql('project', con=c)

But I get this error

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=?...

But I can read data from table. I don't know why it fails.

Answer 1

I think you should set your search_path to the schema where your table is located.

With either:

ALTER USER username SET search_path = schema;

In PostgreSQL.

Or set your search_path in the parameters of psycorg2.connect:

search_path=schema