0

I am trying to store some TV information in a MySQLdb. I have tried about everything and I cannot get the variables to post. There is information in the variables as I am able to print the information.

My Code:

import pytvmaze
import MySQLdb

AddShow = pytvmaze.get_show(show_name='dexter')
MazeID = AddShow.maze_id
ShowName = "Show" + str(MazeID)


show = pytvmaze.get_show(MazeID, embed='episodes')
db = MySQLdb.connect("localhost","root","XXXXXXX","TVshows" )
cursor = db.cursor()

for episode in show.episodes:
   Show = show.name
   ShowStatus = show.status
   ShowSummary = show.summary
   Updated = show.updated
   Season = episode.season_number
   Episode = episode.episode_number
   Title = episode.title
   AirDate = episode.airdate
   ShowUpdate = show.updated
   EpisodeSummary = episode.summary
   try:
      sql =  "INSERT INTO " + ShowName  +  " VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""" (Show,ShowStatus,ShowSummary,Updated,Season,Episode,Title,AirDate,ShowUpdate,EpisodeSummary)
      cursor.execute(sql)
      db.commit()
   except:     
      db.rollback()
db.close()

Any thoughts? Thanks in advance.

EDIT - WORKING CODE 

import pytvmaze
import MySQLdb

AddShow = pytvmaze.get_show(show_name='dexter')
MazeID = AddShow.maze_id
ShowNameandID = "Show" + str(MazeID)

show = pytvmaze.get_show(MazeID, embed='episodes')
db = MySQLdb.connect("localhost","root","letmein","TVshows" )
cursor = db.cursor()

for episode in show.episodes:
   ShowName = show.name
   ShowStatus = show.status
   ShowSummary = show.summary
   Updated = show.updated
   Season = episode.season_number
   Episode = episode.episode_number
   Title = episode.title
   AirDate = episode.airdate
   ShowUpdate = show.updated
   EpisodeSummary = episode.summary    
   sql = "INSERT INTO " + ShowNameandID  +  """ VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
   cursor.execute(sql, (ShowName, ShowStatus, ShowSummary, Updated, Season, Episode, Title, AirDate, ShowUpdate, EpisodeSummary))
   db.commit()
   print sql ##Great for debugging
db.close()
Caseyamn
  • 31
  • 5

1 Answers1

0

First of all, you've actually made things more difficult for yourself by catching all the exceptions via bare try/expect and then silently rolling back. Temporarily remove the try/except and see what the real error is, or log the exception in the except block. I bet the error would be related to a syntax error in the query since you would miss the quotes around the column value(s).

Anyway, arguably the biggest problem you have is how you pass the variables into the query. Currently, you are using string formatting, which is highly not recommended because of the SQL injection attack danger and problems with type conversions. Parameterize your query:

sql = """
INSERT INTO 
    {show}
VALUES 
    (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""".format(show=Show)
cursor.execute(sql, (ShowStatus, ShowSummary, Updated, Season, Episode, Title, AirDate, ShowUpdate, EpisodeSummary))

Note that it is not possible to parameterize the table name (Show in your case) - we are using string formatting for it - make sure you either trust your source, or escape it manually via MySQLdb.escape_string(), or validate it with a separate custom code.

Community
  • 1
  • 1
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • [Note that some escaping methods do not fully prevent sql injection](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – pppery Dec 27 '15 at 22:41
  • @ppperry okay, this is the OP's responsibility to validate the `Show` value - I've added "validate it with a separate custom code" intentionally. For instance, we may require it to be alphanumeric only. Thanks! – alecxe Dec 27 '15 at 22:42
  • Ok I have removed the try/expect and removed the variable table name. Still no go. the new code is in my original post. – Caseyamn Dec 27 '15 at 23:41
  • @Caseyamn now u are missing the commit call. – alecxe Dec 27 '15 at 23:52
  • alecxs Yes I was! Thank you so much! 2 days of struggles solved! – Caseyamn Dec 27 '15 at 23:58