0

i am trying to execute raw sql query on my python application using heroku database. i have created tables by running a script separately. model

i have configured the database as below:

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

when i execute INSERT INTO command i get error. error

INSERT QUERY:

db.execute("INSERT INTO users (username, firstName, lastName, email, password) VALUES (:username, :firstName, :lastName, :email, :password)",
    {"username": username, "firstName": firstName, "lastName": lastName, "email": email, "password": password})
db.commit()
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Ashwin
  • 13
  • 1
  • 3
  • In SQL identifiers are case *insensitive*, and quoted identifiers are case *sensitive* (and can contain punctuation etc.). It looks like you have created your table using the latter, but in your INSERT statement you use the former. – Ilja Everilä May 07 '20 at 09:45

1 Answers1

1

You've described the model in your code, but you didn't create tables in the database itself. Try using db.create_all()

Michail Highkhan
  • 517
  • 6
  • 18
  • i have used another python script to create tables(import.py). you can have a look at my code in github. https://github.com/ashwinpaulallen/WebApp_Python – Ashwin May 07 '20 at 08:18
  • You've done it without application context. Try adding `with app.app_context(): ` before `db.create_all()` – Michail Highkhan May 07 '20 at 08:33
  • Also, using create_engene is not a Flask way and using raw SQL queries is really not an SQLAlchemy way. Check this out https://hackersandslackers.com/flask-sqlalchemy-database-models/ – Michail Highkhan May 07 '20 at 08:36
  • Or here I have an example in one module https://github.com/GreenBlackSky/fun_with_flask/blob/master/flask_sqlalchemy_test.py It is an example, never store your db config in code) – Michail Highkhan May 07 '20 at 08:43
  • thanks for the tip, i am writing raw SQL as part of my learning. i added the change which you suggested. but after i start my application "application.py", when i register a new user i get this error. step 1: run "import.py" -- created all tables and imported data step 2: start flask application (application.py) -- here i am trying a register a new user. when i submit the user form i get this error. this is what i am trying to do. can you suggest something here to fix this error? – Ashwin May 07 '20 at 09:11
  • Have you tried to check columns of this table manually? – Michail Highkhan May 08 '20 at 06:16
  • i was able to fix the issue. I used model class to create tables and then was executing raw SQL query. somehow that didnt work. i created tables using raw SQL and it worked. – Ashwin May 09 '20 at 08:00