appending many variables to MySQL with python

Question

insert_basic = '""' + '"INSERT INTO info VALUES (' + "'" + testid + "','" + username + "','" + fullname + "','" + firstnumber + "','" + secondnumber + "','" + floatnumber + "','" + posts + "','" + country + '","' + bio + '","' + link + '");'
cur.execute(insert_basic)
try: 
    db.commit()
except:
    db.rollback()
cur.execute("""SELECT * FROM basic;""")
print cur.fetchall()

Hello, I have done a lot of research and I cannot seem to get the syntax right for submitting to SQL. Some of the variables are integers and some are strings with one being a float.

alecxe · Accepted Answer · 2017-06-05 19:19:55Z

2

This is what parameterized queries are for:

parameters = (testid, username, fullname, firstnumber, secondnumber, floatnumber, posts, country, bio, link)
query = """
    INSERT INTO 
        info 
    VALUES 
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
cur.execute(query, parameters)

When you do it this way, you are letting your MySQL database driver to handle the type conversions between Python and MySQL automatically.

And, you are also protecting yourself from SQL injection attacks that would be easy to apply to your initial code where you string concatenate your query.

I would also specify the table column names in this case explicitly:

INSERT INTO 
    info 
    (column_name1, column_name2, ...)
VALUES
    (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)

answered Jun 5, 2017 at 19:19

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

Collectives™ on Stack Overflow

appending many variables to MySQL with python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related