Failing SQL-INSERT query when it is executed by a database object from another python script

Question

I´m quiet new to Python and trying my first own projects. At the moment I´m struggeling with an SQL insert of a dataframe to a Postgres database with placeholders for the values. I have one Python Script (database_config) which creates the database object and also the methods like execute(), which I want to call in my main script.

When I try to do the insert with an database connection/cursor which is generated in the main script it works well. But when I try to call the execute() method from the database_config script with the same query-string I get the following error message:

"FEHLER: Syntaxfehler bei »%« LINE 19: VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, ..."

The database-config looks like:

import os
import psycopg2

# password for the database from an environment variable
db_pw = os.environ.get('DB_PASS')
conn_str = "host=localhost user=postgres dbname=nfl_scores_bets password={}".format(db_pw)

class MyDatabase():
    def __init__(self):
        self.conn = psycopg2.connect(conn_str)
        self.cur = self.conn.cursor()

        self.conn.set_session(autocommit=True)

    def query_func(self, query, params=None):
        try:
            self.cur.execute(query)
        except psycopg2.Error as e:
            print(e)

The insert string and the method call from the main script are:

scores_bets_table_insert = ("""INSERT INTO scores_bets (
schedule_date,
schedule_season,
schedule_week,
schedule_playoff,
team_home,
score_home,
score_away,
team_away,
team_favorite_id,
spread_favorite,
over_under_line,
stadium_name,
stadium_neutral,
weather_temperature,
weather_wind_mph,
weather_humidity,
weather_detail) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""")

# works fine, if I create the datbase connection in this  main script
for i, row in scores_bets.iterrows():
    cur.execute(scores_bets_table_insert, list(row))

# doesn´t work when I do the insert with the method from the database_config script
db =MyDatabase()
for i, row in scores_bets.iterrows():
    db.query_func(scores_bets_table_insert, list(row))

Till now I couldn´t figured out what creates the syntax-error or what is the diffenrence between executing the same query string with the ursor from the same script or calling the execute() method from the other script. I couldn´t find the reason in the psycopg2 documentation, hopefully some one of you see the error or can give me a hint what I'm doing wrong.

The change to ? results in the same error. What is the difference between %s and ? ? in the most docs, tutorials the placeholder %s was used. I`m not sure what was my Intention with params = None. — DataSwell
– DataSwell, Commented Sep 30, 2022 at 21:36
With psycopg2 you can use either use %s or the named version %(some_name)s per Parameter passing. In query_func() you need self.cur.execute(query, params) in order to get the list(row) parameters passed into the query for use by the placeholders %s. You are getting the syntax error because psycopg2 has nothing to match with %s so it passing the query through as as Postgres does not know what to do with %s. — Adrian Klaver
– Adrian Klaver, Commented Sep 30, 2022 at 21:48
@AdrianKlaver thank you very much, that was the missing part, which I couldn´t figured out. With adding params to the execute() Method it works well. — DataSwell
– DataSwell, Commented Sep 30, 2022 at 23:59

bitflip · Accepted Answer · 2022-09-30 21:09:03Z

0

You dont have to write your sql manually. Pandas comes with a to_sql method. Try this:

scores_bets.to_sql('scores_bets', db.conn, if_exists='append', index=False)

Note: Make sure the columns in your DataFrame have the same name as your column in your database.

answered Sep 30, 2022 at 21:09

bitflip

3,7391 gold badge6 silver badges22 bronze badges

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

4 Comments

DataSwell Over a year ago

Thanks for your hint. Does this also works for Postgres databases? When I try your code, I get this error message: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': FEHLER: Relation »sqlite_master« existiert nicht LINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...

bitflip Over a year ago

I am using postgres, yes. Your error does not seem to be related to the command above... Is your tablename scores_bets?

Adrian Klaver Over a year ago

Looks like you have the connection configured for SQLite and SQLAlchemy is doing a lookup on the SQLite system table sqlite_master for information on the table the to_sql is running against.

bitflip Over a year ago

thanks for your comment, i think you are right about that.

Collectives™ on Stack Overflow

Failing SQL-INSERT query when it is executed by a database object from another python script

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related