I'm trying co insert rows into a table, one column of which is GPS coordinates. Rows will later be selected based on their distance from a given set of coordinates, so I want to use the POINT data type to make use of the inbuilt function to do that.
My program builds up the query string and correct number of '%s' placeholders for the values correctly for all the other data, but when I try to do the same with the spatial data I can't get it to work, giving
Error #1416 - Cannot get geometry object from data you send to the GEOMETRY field".
Spatial data string is: "ST_GeomFromText('POINT(2 2)', 4326)"
If I put the exact string directly into the query it runs correctly, but if I think put that string into a variable and insert with %s I get the above error.
point2 = "ST_GeomFromText('POINT(2 2)', 4326)"
my_name = "Test1"
point = "POINT(3 3)"
# Placeholder for "name" string, no placeholder for POINT. This works correctly:
mycursor.execute(
"INSERT INTO person (name, gps) VALUES (%s, ST_GeomFromText('POINT(2 3)', 4326));",
(my_name,),
)
# Placeholder for POINT string, works correctly:
mycursor.execute(
"INSERT INTO person (name, gps) VALUES (%s, ST_GeomFromText(%s, 4326));",
(my_name, point),
)
# This is what I want to do, but won't work:
mycursor.execute(
"INSERT INTO person (name, gps) VALUES (%s, %s);",
(my_name3, point2),
)
It seems as soon as I try to use a placeholder for the ST_GeomFromText section, it doesn't work. However I do thet the error message that shows it's trying to interpret the data as spatial, so it is recognising it.
I feel like I must be missing something obvious, but I've spent several hours trying different combinations and can't get it to work.
If anyone is willing to put a second pair of eyes on this I would appreciate it enormously!
Thank you.
EDIT:
I'm using mysql.connector version 8.1.0
From the logs I can see that the working query is submitted as:
INSERT INTO person (name, gps) VALUES ('Test11asd', ST_GeomFromText('POINT(3 3)', 4326))
Whereas the non working query is submitted as:
INSERT INTO person (name, gps) VALUES ('Test3', 'ST_GeomFromText(\'POINT(2 2)\', 4326)')
The difference being that using the placeholder it as added additional quotes around the 'ST_GeomFromText' section. I presume because it is trying to insert a string into the query string. However I can see no way to remove those, as I have to save it as a string in the python variable, ie
point2 = "ST_Geom..."
I can't do :
point2 = ST_Geom...