2

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...
0

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.