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

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. — Ricardo C
– Ricardo C, Commented Aug 21, 2017 at 21:44
i usually use \ to escape the quoted character to executing sql in python. Something like this CREATE TABLE \'app_users\' ( ... — Alam
– Alam, Commented Aug 21, 2017 at 21:52
i replaced ` with ' and \' . Both result in the examt same error.. — Creative crypter
– Creative crypter, Commented Aug 21, 2017 at 21:54
i removed them all, results in: psycopg2.ProgrammingError: syntax error at or near "(" ... any help? — Creative crypter
– Creative crypter, Commented Aug 21, 2017 at 22:11
Forget about my previous comment. It seems that your question should be "How to convert this MySQL create table script to the PostgreSQL" — Abelisto
– Abelisto, Commented Aug 21, 2017 at 22:20

Clodoaldo Neto · Accepted Answer · 2017-08-22 13:02:39Z

1

Use the proper Postgresql syntax:

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

answered Aug 22, 2017 at 13:02

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

python3 - psycopg2 - execute sql file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related