CODEWITHSUNDEEP
pythonsqlpymysql
Tiberiu
Tiberiu

Reputation: 1030

Python pymysql INSERT INTO inserting empty values

I'm trying to insert some values in a table, and although the rows are being created, the values aren't being recorded. Here is my code:

for i in range(2,6):
    for team in ul[i]:
        name = team.string #string from html element
        print(name) #this works just fine, and prints the desired name
        cur.execute("INSERT INTO teams (Name) VALUES(name)")
conn.commit()

Now if I put VALUES("Test String") instead, it works, 30 rows are added (what I want), and all with the Name: "Test String".

Yet when I put in my name variable, the rows are added as well, but the column values are empty. The column I'm putting the strings in is VARCHAR. Is there something I don't know about how the SQL statement is interpreted in the case of Python string variables?

Upvotes: 0

Views: 1868

Answers (1)

Alex Woolford
Alex Woolford

Reputation: 4563

It appears that the SQL statement is a simple string and he name variable isn't being inserted into it.

Perhaps you could do something like this:

sql = """INSERT INTO teams (Name) VALUES({0})""".format(json.dumps(name))
cur.execute(sql)

I use json.dumps(myVar) to escape special characters, e.g. quotes, etc... that might break the SQL insert statement.

Sometimes it's helpful to print out the SQL statement and try to run it in a client (e.g. MySQL Workbench), in order to see what changes, if any, are necessary to generate syntactically correct statements.

Upvotes: 1

Related Questions