python3 - psycopg2 - execute sql file

Question

i have this basic sql file:

CREATE TABLE `app_users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `email` varchar(200) DEFAULT NULL,
 `password` varchar(200) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and want to execute it via python:

connect_str = "dbname='dbname' user='user' host='192.168.1.101' password='password'"
conn = psycopg2.connect(connect_str)
cursor = conn.cursor()
fd = open('file.sql', 'r')
sqlFile = fd.read()
fd.close()
sqlCommands = sqlFile.split(';')
for command in sqlCommands:
 print(command)
 if command.strip() != '':
 cursor.execute(command)

When i execute this via "python3 app.py", it connects, but i just get:

psycopg2.ProgrammingError: syntax error at or near "`"
LINE 1: CREATE TABLE `app_users` (

But i have no idea why.. anybody could help me with this issue?

thanks and greetings

• Ricardo C: I suggest to look into the type of quote you are using. This ` is different to this ' and each programming language will have different ways to interpret them, parsing and escaping.
• Alam: i usually use \ to escape the quoted character to executing sql in python. Something like this CREATE TABLE \'app_users\' ( ...
• Creative crypter: i replaced ` with ' and \' . Both result in the examt same error..
• Creative crypter: i removed them all, results in: psycopg2.ProgrammingError: syntax error at or near "(" ... any help?
• Abelisto: Forget about my previous comment. It seems that your question should be "How to convert this MySQL create table script to the PostgreSQL"

Answer 1

Use the proper Postgresql syntax:

CREATE TABLE app_users (
 id serial,
 email varchar(200),
 password varchar(200),
 primary key (id)
)