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

• sagi: You are missing space after the INNER JOIN games .
• v18o: @sagi thanks, but there is another mistake

Answer 1

@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.

• Tbaker: 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: @Tbizzness great point. Completely agree, we need a parameterized query, updated - thanks!