PYTHON INSERT MYSql query

Question

I'm trying to execute that code in python 2.7 :

import MySQLdb, getopt
import MySQLdb.cursors
a = "TEST"
b = 1
c = 2
d = 3
e = 4
db = MySQLdb.connect(host="localhost", user="root",passwd="password", 
db="database") # name of the data base
cur = db.cursor(MySQLdb.cursors.DictCursor)
query = ""INSERT INTO `HD_coords` (`name`, `west`, `north`, `east`, `south`) 
VALUES (%s, %s, %s, %s, %s)"",(a, b, c, d, e)
cur.execute(query)
cur.close() 
db.commit()

So i'm getting this error :

Traceback (most recent call last):
  File "MYSQL_TEST.py", line 15, in <module>
    cur.execute(query)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 223, in 
execute
    self.errorhandler(self, TypeError, m)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in 
defaulterrorhandler
    raise errorvalue
TypeError: query() argument 1 must be string or read-only buffer, not tuple

nom is varchar type(255). west, north, south, east are int type. Any clue?

Thxx

Tim Biegeleisen · Accepted Answer · 2018-06-10 09:19:08Z

1

Your syntax is off. The code doing the insertion should look something like this:

cur = db.cursor(MySQLdb.cursors.DictCursor)
query = ("INSERT INTO HD_coords (name, west, north, east, south) " +
         "VALUES (%s, %s, %s, %s, %s)")
cur.execute(query, (a, b, c, d, e))
cur.close() 
db.commit()

First define a SQL statement with placeholders, which you already seem to be doing. Then, bind values to those placeholders in your call to cursor.execute.

answered Jun 10, 2018 at 9:19

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

manan5439 · Accepted Answer · 2018-06-10 09:15:10Z

0

You have to put your connection object in try and expect block that can handle your connections exception

This error occurred because it's give exception before exception

answered Jun 10, 2018 at 9:15

manan5439

9589 silver badges27 bronze badges

Comments

A. Colonna · Accepted Answer · 2018-06-10 22:16:13Z

0

You can use the .format(**locals()) which replace variable by their values:

cur = db.cursor(MySQLdb.cursors.DictCursor)
query = "INSERT INTO HD_coords (name, west, north, east, south) VALUES ({a},{b}, {c}, {d}, {e})".format(**locals())
cur.execute(query)
cur.close() 
db.commit()

answered Jun 10, 2018 at 22:16

A. Colonna

8827 silver badges10 bronze badges

Collectives™ on Stack Overflow

PYTHON INSERT MYSql query

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related