Python pymysql INSERT INTO inserting empty values

Question

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?

Alex Woolford · Accepted Answer · 2014-10-12 23:41:22Z

1

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.

edited Oct 12, 2014 at 23:41

answered Oct 12, 2014 at 23:35

Alex Woolford

4,58311 gold badges51 silver badges82 bronze badges

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

2 Comments

Tiberiu Over a year ago

This works, thanks! I found that the type of my name variable was <class 'bs4.element.NavigableString'>, so that's probably why the query didn't recognize it

tuomur Over a year ago

Considering that name is coming from a HTML form or something like that, you really should use query parameters for the insert. String formatting opens a chance for SQL injection.

Collectives™ on Stack Overflow

Python pymysql INSERT INTO inserting empty values

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