psycopg2.ProgrammingError: can't execute an empty query

[mzealey]

I get this err on the 1st table (dumped the sql that it was trying to execute each time)

STARTING <<<<<<<<<<

START CREATING TABLES START - CREATING TABLE city DROP SEQUENCE IF EXISTS "city_id_seq" CASCADE; CREATE SEQUENCE "city_id_seq" INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; SELECT pg_catalog.setval('"city_id_seq"', 8, true); DROP TABLE IF EXISTS "city" CASCADE; CREATE TABLE "city" ( "id" integer DEFAULT nextval('"city_id_seq"'::regclass) NOT NULL, "name" character varying(32) NOT NULL, "pathname" character varying(32) NOT NULL, "latitude" numeric(9, 6) NOT NULL, "longditude" numeric(9, 6) NOT NULL ) WITHOUT OIDS;

Traceback (most recent call last): File "/usr/local/bin/py-mysql2pgsql", line 5, in pkg_resources.run_script('py-mysql2pgsql==0.1.6', 'py-mysql2pgsql') File "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 528, in run_script self.require(requires)[0].run_script(script_name, ns) File "/usr/lib/python2.7/dist-packages/pkg_resources.py", line 1394, in run_script execfile(script_filename, namespace, namespace) File "/usr/local/lib/python2.7/dist-packages/py_mysql2pgsql-0.1.6-py2.7.egg/EGG-INFO/scripts/py-mysql2pgsql", line 38, in mysql2pgsql.Mysql2Pgsql(options).convert() File "/usr/local/lib/python2.7/dist-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/mysql2pgsql.py", line 37, in convert Converter(reader, writer, self.file_options, self.run_options.verbose).convert() File "/usr/local/lib/python2.7/dist-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/converter.py", line 32, in convert self.writer.write_table(table) File "/usr/local/lib/python2.7/dist-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/init.py", line 91, in decorated_function ret = f(_args, *_kwargs) File "/usr/local/lib/python2.7/dist-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_db_writer.py", line 155, in write_table self.execute(sql) File "/usr/local/lib/python2.7/dist-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_db_writer.py", line 109, in execute cur.execute(sql, args) psycopg2.ProgrammingError: can't execute an empty query

Just adding an if len(sql): to the execute function fixes it but no idea where the blank sql would come from.

[osvarychevskyi]

I found why this issue happens.

py-mysql2pgsql/mysql2pgsql/lib/postgres_writer.py line 242

table_sql.append( self.table_comments(table))

if table has no comments then this line generates empty string elements. This array element then is executing in py-mysql2pgsql/mysql2pgsql/lib/postgres_db_writer.py line 152,153,154

table_sql, serial_key_sql = super(PostgresDbWriter, self).write_table(table)
for sql in serial_key_sql + table_sql:
    self.execute(sql)

but sql variable is empty string.

As a result psycopg2 library throws an exception "can't execute an empty query". Maybe this behavior was changed in last version.

My quick solution was to add

for sql in serial_key_sql + table_sql:
    if sql != "":
        self.execute(sql)

But better solution would be to fix it in py-mysql2pgsql/mysql2pgsql/lib/postgres_writer.py.

[shprabin]

Thank you @osvarychevskyi. your solution to change in file py-mysql2pgsql/mysql2pgsql/lib/postgres_db_writer.py and reinstalling/recompiling py-mysql2pgsql worked for me. Saved my day. Thanks.

for sql in serial_key_sql + table_sql:
    if sql != "":
        self.execute(sql)

[kworr]

Thank you, I went for reworking how table comments are processed so there would be no empty commands.

[sdklab007]

I had the same issue, but however replacing the single line comments with multi-line comments solved the issue.

E.g:

-- 
-- creates user tables without constraints or indexes
--

Modified with

/*
 creates user tables without constraints or indexes
*/

Modified one is working.

[SrNetoChan]

I am also having this issue. If I start the query with a single line comment I will get the error. But single line comments in the middle of the SQL query is not a problem. Should this be reopened, or should a new issue be created?