Query using TRUNCATE fails about db.py

[ericchiang]

The query function doesn't do anything special if you just give it a string, it just defers to pandas. I suspect this is a driver issue.

Can you tell me the full results of the following code?

import psycopg2 as pg
import pandas as pd
# connect to your redshift database
con = pg.connect(user="username", password="password", host="host", port=port, dbname="dbname")
result = pd.io.sql.read_sql("TRUNCATE table_name;", con)
print result

[ppope]

Thanks for the reply.

Running the following (with my Redshift credentials of course)

import psycopg2 as pg
import pandas as pd
# connect to your redshift database
con = pg.connect(user="username", password="password", host="host", port=port, dbname="dbname")
#
#
pd.io.sql.read_sql("TRUNCATE table_name;", con)

Obtains:

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-15-88f7d66aab7b> in <module>()
      8 #
      9 #
---> 10 pd.io.sql.read_sql("TRUNCATE table_name;", con)

/home/phil/anaconda3/envs/python2/lib/python2.7/site-packages/pandas/io/sql.pyc in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns)
    419         return pandas_sql.read_sql(
    420             sql, index_col=index_col, params=params,
--> 421             coerce_float=coerce_float, parse_dates=parse_dates)
    422 
    423     if pandas_sql.has_table(sql):

/home/phil/anaconda3/envs/python2/lib/python2.7/site-packages/pandas/io/sql.pyc in read_sql(self, sql, index_col, coerce_float, params, parse_dates)
   1044                  parse_dates=None):
   1045         args = _convert_params(sql, params)
-> 1046         cursor = self.execute(*args)
   1047         columns = [col_desc[0] for col_desc in cursor.description]
   1048         data = self._fetchall_as_list(cursor)

/home/phil/anaconda3/envs/python2/lib/python2.7/site-packages/pandas/io/sql.pyc in execute(self, *args, **kwargs)
   1039 
   1040             ex = DatabaseError("Execution failed on sql: %s" % args[0])
-> 1041             raise_with_traceback(ex)
   1042 
   1043     def read_sql(self, sql, index_col=None, coerce_float=True, params=None,

/home/phil/anaconda3/envs/python2/lib/python2.7/site-packages/pandas/io/sql.pyc in execute(self, *args, **kwargs)
   1028                 cur.execute(*args, **kwargs)
   1029             else:
-> 1030                 cur.execute(*args)
   1031             return cur
   1032         except Exception as e:

DatabaseError: Execution failed on sql: TRUNCATE table_name;

[ppope]

I'm not sure if this is helpful but I noted that queries like

db.query("select count(*) from table_name;")

work as expected on my machine

[bmabey]

The bug is in pandas (or the driver as @ericchiang as mentioned) and not in db.py. I don't know if it is specific to redshift but the underlying issue is that the TRUNCATE and other commands are just that- commands and not queries. I ran into the same issue when trying to set my schema search path. Instead of using the query method using the cursor directly is the better approach, for example:

db.cur.execute("set search_path to blah;")

[ppope]

OK, thanks. Closing this as it is not a bug in db.py.