CODEWITHSUNDEEP
pythonmysql
tlre0952b
tlre0952b

Reputation: 751

Inserting into MySQL from Python - Errors

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.

Upvotes: 1

Views: 8944

Answers (3)

Aya
Aya

Reputation: 42080

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.

Upvotes: 2

Sylvain Leroux
Sylvain Leroux

Reputation: 52060

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.

Upvotes: 0

Dan Allan
Dan Allan

Reputation: 35265

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.

Upvotes: 0

Related Questions