Send spatial data using Python and Mysql Connector

244 Views Asked by At

Using Mysql Connector and Python I am trying to pass the simplest form of spatial data (Point datatype) to my MySQL database. Using the mysql connector the 'execute' function in Option1 works whereas the same command in Option2 does not work giving me the "You have an error in your SQL syntax" error.

Could you please share any thoughts why the Option 2 "mycursor.execute(sql, val)" gives me an error?

import mysql.connector
from Functions import *
mydb = mySqlConnection()
mycursor = mydb.cursor()
Latitude=5.245 # Values are random
Longitude=1.66 # Values are random
Location = "ST_GeomFromText('POINT("+str(Latitude)+" "+str(Longitude)+")')"

#OPTION 1 - WORKS
sql = "INSERT INTO test (Location) VALUES ("+Location+")"
mycursor.execute(sql)

#OPTION 2 - DOES NOT WORK
sql = "INSERT INTO test (Location) VALUES (%s)"
val = (Location) 
mycursor.execute(sql, val)

mydb.commit()

Any ideas will be really appreciated. Many thanks in advance.

Best, Alex

0

There are 0 best solutions below