Inserting into MySQL from Python - Errors

Question

I am trying to write a record into a MySQL DB where I have defined table jobs as:

CREATE TABLE jobs(
 job_id INT NOT NULL AUTO_INCREMENT,
 job_title VARCHAR(300) NOT NULL,
 job_url VARCHAR(400) NOT NULL,
 job_location VARCHAR(150),
 job_salary_low DECIMAL(25) DEFAULT(0),
 job_salary_high DECIMAL(25), DEFAULT(0),
 company VARCHAR(150),
 job_posted DATE,
 PRIMARY KEY ( job_id )
);

The code I am testing with is:

cur.execute("INSERT INTO jobs VALUES(DEFAULT, '"+jobTitle+"','"
 +jobHref+"',"+salaryLow+","+salaryHigh+",'"+company+"',"
 +jobPostedAdjusted+"','"+salaryCurrency+"';")
print(cur.fetchall())

The errors that I am getting are:

pydev debugger: starting
Traceback (most recent call last):
 File "C:\Users\Me\AppData\Local\Aptana Studio 3\plugins\org.python.pydev_2.7.0.2013032300\pysrc\pydevd.py", line 1397, in <module>
 debugger.run(setup['file'], None, None)
 File "C:\Users\Me\AppData\Local\Aptana Studio 3\plugins\org.python.pydev_2.7.0.2013032300\pysrc\pydevd.py", line 1090, in run
 pydev_imports.execfile(file, globals, locals) #execute the script
 File "C:\Users\Me\Documents\Aptana Studio 3 Workspace\PythonScripts\PythonScripts\testFind.py", line 25, in <module>
 +jobPostedAdjusted+"','"+salaryCurrency+"';")
TypeError: cannot concatenate 'str' and 'float' objects

What is the best way insert this record? Thanks.

• dm03514: please don't build a query as a string, use parameterized queries so that you don't leave yourself open to sql injection

Answer 1

What is the best way insert this record?

Use %s placeholders, and pass your parameters as a separate list, then MySQLdb does all the parameter interpolation for you.

For example...

params = [jobTitle,
 jobHref,
 salaryLow,
 salaryHigh,
 company,
 jobPostedAdjusted,
 salaryCurrency]
cur.execute("INSERT INTO jobs VALUES(DEFAULT, %s, %s, %s, %s, %s, %s, %s)", params)

This also protects you from SQL injection.

---

Update

I have print(cur.fetchall()) after the cur.execute.... When the code is run, it prints empty brackets such as ().

INSERT queries don't return a result set, so cur.fetchall() will return an empty list.

When I interrogate the DB from the terminal I can see nothing has been changed.

If you're using a transactional storage engine like InnoDB, you have explicitly commit the transaction, with something like...

conn = MySQLdb.connect(...)
cur = conn.cursor()
cur.execute("INSERT ...")
conn.commit()

If you want to INSERT lots of rows, it's much faster to do it in a single transaction...

conn = MySQLdb.connect(...)
cur = conn.cursor()
for i in range(100):
 cur.execute("INSERT ...")
conn.commit()

...because InnoDB (by default) will sync the data to disk after each call to conn.commit().

Also, does the commit; statement have to be in somewhere?

The commit statement is interpreted by the MySQL client, not the server, so you won't be able to use it with MySQLdb, but it ultimately does the same thing as the conn.commit() line in the previous example.

• tlre0952b: The penny isn't dropping. I've tried this but to no avail? I have print(cur.fetchall()) after the cur.execute.... When the code is run, it prints empty brackets such as (). When I interrogate the DB from the terminal I can see nothing has been changed. Also, does the commit; statement have to be in somewhere?
• tlre0952b: Your updated answer clears things up; also having added con.commit() is now writing records to the DB. Thanks.

Answer 2

When you want A1, you can't concatenate like 'A' + 1. As Sylvain just chimed in, format works:

'A{}'.format(1)

but in this case, the execute method provides its own way to handle this common issue.

execute('A%s', (1))

See the documentation for more complete examples.

Answer 3

The error message say it all:

TypeError: cannot concatenate 'str' and 'float' objects

Python does not convert automagicaly floats to string. Your best friend here could be format

>>> "INSERT INTO jobs VALUES(DEFAULT, '{}', '{}')".format("Programmer", 35000.5)
"INSERT INTO jobs VALUES(DEFAULT, 'Programmer', '35000.5')"

But, please note that insertion of user provided data in a SQL string without any precautions might lead to SQL Injection! Beware... That's why execute provide its own way of doing, protecting you from that risk. Something like that:

>>> cursor.execute("INSERT INTO jobs VALUES(DEFAULT, '%s', '%f', "Programmer", 35000.5)

For a complete discussion about this, search the web. For example http://love-python.blogspot.fr/2010/08/prevent-sql-injection-in-python-using.html

---

And, btw, the float type is mostly for scientific calculation. But it is usually not suitable for monetary values, due to rounding errors (that's why your table use a DECIMAL column, and not FLOAT one, I assume). For exact values, Python provide the decimal type. You should take a look at it.

• tlre0952b: I'm glad you've pointed out the discrepancy in using floats for monetary values. The reason floats is being used is because of my earlier post here: stackoverflow.com/questions/17057406/… after I implemented @theodox's code. If you take a look there's more clarity there. But now I'm concerned about this decimal/float issue. What would you suggest? I'm a beginner btw, hence decisions made.