Search code examples
pythonsqlpymysql

Why does INSERT INTO fail with 'Operand should contain 1 column(s)'?

I know that there's literally dozens of questions about the same error, and I've checked them all. Most of them have to do with someone misusing the SELECT statement, and I couldn't find any with a problem similar to mine.

conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root', 
                       passwd='password',
                       db='nhl')
cur = conn.cursor()

#some code...

player = td.string.strip()
player = player.split(' (')
tID = teamList.index(team)
cur.execute("INSERT INTO players (Name,G,A,P,PlusMinus,PIM,S,H,BKS,GVA,TKA,TeamID) 
             VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
            (player, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, tID))

pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

I'm really not sure where I'm going wrong. All database columns are INT except for Name which is VARCHAR. This is coded in Python 3.4 using pymysql.

Solution 
  • player = player.split(' (')

    After this line, player is a list of strings (instead of a string), so cur.execute won't let you interpolate it as the value for the Name column.

    A simple way of finding these bugs is trying to replace the values with literals, like:

    cur.execute("INSERT INTO players (Name,G,A,P,PlusMinus,PIM,S,H,BKS,GVA,TKA,TeamID) 
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                          ("playerName", 0,0,0,0,0,0,0,0,0,0, 123))

    which would work, and you'd know to look for the error elsewhere. See: How to debug by splitting the problem space.