构建数据录入表单时GeomFromText插入SpatiaLite报错求助

Ah, I see the issue here — you're trying to use SQL parameter placeholders (?) inside a quoted string, which won't work like you expect. Let me break this down for you:

The core problem is that when you write GeomFromText('POINT(? ?)', 4326), those two ? are wrapped in single quotes. Your database driver doesn't recognize them as parameter placeholders; instead, it sends the literal string 'POINT(? ?)' to SpatiaLite, which throws a syntax error because that's not valid WKT (Well-Known Text) format. On top of that, your parameter order is completely messed up — the first two values in your tuple (longitude.get() and latitude.get()) would end up being mapped to the next placeholders in the query (starting with Datestamp), which is definitely not what you want.

There are two clean, reliable ways to fix this:

Option 1: Use SpatiaLite's MakePoint function (recommended) #

SpatiaLite has a built-in MakePoint function that takes longitude, latitude, and SRID directly, so you don't need to mess with WKT strings at all. This is safer, avoids string-handling bugs, and is more straightforward:

date = datetime.date(int(year.get()), int(month.get()), int(day.get()))
narratif = T.get("1.0", "end-1c")

# Use MakePoint to create the geometry directly from coordinates
c.execute("""
INSERT INTO Incidents (
    Geometry, Datestamp, Description, Place, Latitude, Longitude, 
    Precision, Intimidation, Destruction, Burglary, Carjacking, Theft, 
    Assault, Sexualassault, Abduction, Homicide, Shooting, Explosive, Narrative
) VALUES(
    MakePoint(?, ?, 4326), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)""", (
    longitude.get(), latitude.get(), date, description.get(), place.get(),
    latitude.get(), longitude.get(), precision.get(), intimidation1.get(),
    destruction2.get(), burglary3.get(), carjacking4.get(), theft5.get(),
    assault6.get(), sexualassault7.get(), abduction8.get(), homicide9.get(),
    shooting10.get(), explosive11.get(), narratif
))
con.commit()

Option 2: Build a valid WKT string first #

If you prefer to stick with GeomFromText, construct the full WKT string in Python first, then pass it as a parameter. Just ensure your longitude/latitude values are valid numbers to avoid syntax issues:

date = datetime.date(int(year.get()), int(month.get()), int(day.get()))
narratif = T.get("1.0", "end-1c")

# Create the properly formatted POINT WKT string
point_wkt = f"POINT({longitude.get()} {latitude.get()})"

# Pass the WKT string as a parameter to GeomFromText
c.execute("""
INSERT INTO Incidents (
    Geometry, Datestamp, Description, Place, Latitude, Longitude, 
    Precision, Intimidation, Destruction, Burglary, Carjacking, Theft, 
    Assault, Sexualassault, Abduction, Homicide, Shooting, Explosive, Narrative
) VALUES(
    GeomFromText(?, 4326), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)""", (
    point_wkt, date, description.get(), place.get(), latitude.get(),
    longitude.get(), precision.get(), intimidation1.get(), destruction2.get(),
    burglary3.get(), carjacking4.get(), theft5.get(), assault6.get(),
    sexualassault7.get(), abduction8.get(), homicide9.get(), shooting10.get(),
    explosive11.get(), narratif
))
con.commit()

A quick note: Option 1 is the better choice because it eliminates any potential SQL injection risks (even if low in your form-based workflow) and keeps your code cleaner. Just double-check that the order of parameters in your tuple matches exactly with the order of placeholders in the VALUES clause — that's a super common pitfall here!

内容的提问来源于stack exchange，提问作者Pan