Search code examples
pythonpostgresqlflaskheroku

Adding Postgresql to my Heroku app - issues with syntax crashing queries?

I'm teaching myself Python, Flask, and adding Postgresql to the mix. I recently deployed my first app on Heroku that didn't use database at all, but now I'm wanting to add on functionality. However, I'm stuck at what feels like a very simple step in the process. Please keep in mind, I'm very, very new to postgres. I had all of these features working perfectly through MySQL on a localhost database, but can't get it working remotely in Postgres :(

My check_connection() works, my create_table() worked, but my pg_add_data() fails, as you'll see at the bottom. I'm doing all my coding inside of PyCharm CE.

import psycopg2

# connect to remote Heroku Postgres DB
pgdb = psycopg2.connect(
   host='ec2-54-211-160-34.compute-1.amazonaws.com',
   user='xxxxxxxx',
   password='xxxxxxxxxxxxxxxxxxxxxxxx',
   port='5432',
   database='df5p5d20v6pbf9'
   )

pgcursor = pgdb.cursor()

# verify I've got my credentials in correctly
def check_connection():
   pgcursor.execute("select version()")
   data = pgcursor.fetchone()
   print("Connection established to: ", data)

def create_table():
   sql = '''CREATE TABLE estimation_data 
   (User text PRIMARY KEY,
    Motor_kV integer,
     Batt_Volt decimal,
      Pinion integer,
       Spur integer,
        Final_Gearing decimal,
         Wheel_Rad decimal);'''
   pgcursor.execute(sql)
   pgdb.commit()

def pg_add_data(sql,val):
   pgcursor.executemany(sql, val)
   pgdb.commit()

pgsql = '''INSERT INTO estimation_data 
   (User, Motor_kV, Batt_Volt, Pinion, Spur, Final_Ratio, Wheel_Rad) 
   VALUES (%s, %s, %s, %s, %s, %s, %s);'''
pgval = [
      ('204.210.165.122', 2400, 16.8, 16, 54, 3.92, 2.5),
      ('204.210.165.123', 3500, 12.6, 16, 54, 3.92, 2.5),
      ('204.210.165.124', 4200, 8.4, 12, 54, 3.92, 2.5)]

pg_add_data(pgsql,pgval)

When I execute the pg_add_data line at the bottom, I'm getting:

Traceback (most recent call last):
  File "C:\Users\prodsupervisor\PycharmProjects\RC_Speed_Estimator\db_handling.py", line 119, in <module>
    pg_add_data(pgsql,pgval)
  File "C:\Users\prodsupervisor\PycharmProjects\RC_Speed_Estimator\db_handling.py", line 96, in pg_add_data
    pgcursor.executemany(sql, val)
psycopg2.errors.SyntaxError: syntax error at or near "User"
LINE 2:    (User, Motor_kV, Batt_Volt, Pinion, Spur, Final_Ratio, Wh...
            ^


Process finished with exit code 1

I'm tried to decipher the answer to what syntax error is at or near User.... I'm stumped. I've been trying to follow the tutorial https://www.tutorialspoint.com/python_data_access/python_postgresql_insert_data.htm with the help of https://www.postgresqltutorial.com/ . I also have pgAdmin4 installed, but I'm baffled by that program at this phase of my learning (I much prefer MySQL Workbench, so far).

Greatly appreciate all help and tips!

edit 1: Tried using "" around column names, per charchit's suggestion, got this:

Traceback (most recent call last):
  File "C:\Users\prodsupervisor\PycharmProjects\RC_Speed_Estimator\db_handling.py", line 119, in <module>
    pg_add_data(pgsql,pgval)
  File "C:\Users\prodsupervisor\PycharmProjects\RC_Speed_Estimator\db_handling.py", line 96, in pg_add_data
    pgcursor.executemany(sql, val)
psycopg2.errors.UndefinedColumn: column "User" of relation "estimation_data" does not exist
LINE 2:    (`User`, `Motor_kV`, `Batt_Volt`, `Pinion`, `Spur`, `Fina...
            ^


Process finished with exit code 1
Solution

  • User is a reserved word in postgresql and is escaped by enclosing it with "" See https://stackoverflow.com/a/7651432. In contrast to mysql where the escaping characters are backticks.

    You also try to insert into a column named Final_Ratio but define a column Final_Gearing. For testing I#m also dropping and recreating the table each run. The fixed code:

    import psycopg2

# connect to remote Heroku Postgres DB
pgdb = psycopg2.connect(
   host='localhost',
   user='test',
   password='test',
   port='5432',
   database='test_stackoverflow'
   )

pgcursor = pgdb.cursor()

# verify I've got my credentials in correctly
def check_connection():
   pgcursor.execute("select version()")
   data = pgcursor.fetchone()
   print("Connection established to: ", data)

def create_table():
   sql = '''CREATE TABLE estimation_data 
   ("User" text PRIMARY KEY,
    Motor_kV integer,
     Batt_Volt decimal,
      Pinion integer,
       Spur integer,
        Final_Gearing decimal,
         Wheel_Rad decimal);'''
   pgcursor.execute(sql)
   pgdb.commit()

def drop_table():
   sql = "DROP TABLE IF EXISTS estimation_data;"
   pgcursor.execute(sql)
   pgdb.commit()



def pg_add_data(sql,val):
   pgcursor.executemany(sql, val)
   pgdb.commit()


check_connection()
drop_table()
create_table()

pgsql = '''INSERT INTO estimation_data 
   ("User", Motor_kV, Batt_Volt, Pinion, Spur, Final_Gearing, Wheel_Rad) 
   VALUES (%s, %s, %s, %s, %s, %s, %s);'''
pgval = [
      ('204.210.165.122', 2400, 16.8, 16, 54, 3.92, 2.5),
      ('204.210.165.123', 3500, 12.6, 16, 54, 3.92, 2.5),
      ('204.210.165.124', 4200, 8.4, 12, 54, 3.92, 2.5)]
pg_add_data(pgsql, pgval)

    While it is good to learn the basics for a production site I would strongly recommend using a high level library like SQLalchemy.