Execute select on sqlite db using pythonic data

Question

I have sqlite select query and can't see what is going wrong.

team_id, season_type, season, game_date = 'some_id', '2014-15', 'Some Season', '2015-00-00T00:00:00'

cur.execute('SELECT teams_stats_in_game.rebounds FROM teams_stats_in_game '
                        'INNER JOIN games'
                            'ON games.id = teams_stats_in_game.game_id '
                                'AND games.home_team_id = teams_stats_in_game.team_id '
                        'WHERE games.home_team_id = %s '
                            'AND games.season_type = %s '
                            'AND games.season = %s '
                            'AND games.date_est < %s '
                        'ORDER BY games.date_est DESC' % (team_id, season_type, season, game_date))

Trace:

Traceback (most recent call last):
'ORDER BY games.date_est DESC' % (team_id, season_type, season, game_date))
OperationalError: near "Season": syntax error

You are missing space after the INNER JOIN games .

sagi
– sagi

2016-02-24 14:52:18 +00:00
Commented Feb 24, 2016 at 14:52 — sagi
– sagi, Commented Feb 24, 2016 at 14:52
@sagi thanks, but there is another mistake

v18o
– v18o

2016-02-24 14:56:16 +00:00
Commented Feb 24, 2016 at 14:56 — v18o
– v18o, Commented Feb 24, 2016 at 14:56

alecxe · Accepted Answer · 2016-02-24 14:59:04Z

3

@sagi has a good point. I would though recommend using a multi-line string for the query:

cur.execute("""
    SELECT 
        teams_stats_in_game.rebounds 
    FROM 
        teams_stats_in_game 
        INNER JOIN games 
        ON games.id = teams_stats_in_game.game_id AND 
           games.home_team_id = teams_stats_in_game.team_id
        WHERE games.home_team_id = ? AND 
              games.season_type = ? AND 
              games.season = ? AND 
              games.date_est < ? 
    ORDER BY 
        games.date_est DESC""", (team_id, season_type, season, game_date))

Also note that I've replaced %s with ? placeholders and now passing the query parameters in the second argument to execute() which makes this query parameterized.

edited Feb 24, 2016 at 14:59

answered Feb 24, 2016 at 14:55

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

2 Comments

Tbaker Over a year ago

I believe that % is the wild card in SQLite, and looking at the query it would make more sense to write it as: (‘SELECT teams_stats_in_game.rebounds FROM teams_stats_in_game INNER JOIN games ON games.id = teams_stats_in_game.game_id AND games.home_team_id = teams_stats_in_game.team_id WHERE games.home_team_id = ? AND games.season_type = ? AND games.season = ? AND games.date_est < ? ORDER BY games.date_est DESC""" ?’, [team_id, season_type, season, game_date])

alecxe Over a year ago

@Tbizzness great point. Completely agree, we need a parameterized query, updated - thanks!

Collectives™ on Stack Overflow

Execute select on sqlite db using pythonic data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related