Inserting geometry into spatialite - no geometry

Question

I have created a test spatialite database and I am having trouble inserting geometry into the table. I have successfully created the geometry column in the table and then I run the insert command. The weird thing is that it runs without error, and the attributes get passed in, however the geometry column is empty when I view it through a ODBC connection, as well as no geometry coming through in QGIS.

My code to create the table:

with sqlite3.connect(r'somedatabase.sqlite') as conn:

    conn.enable_load_extension(True)

    conn.execute("SELECT load_extension('mod_spatialite')")
    conn.execute("SELECT InitSpatialMetaData()")

    c = conn.cursor()


    c.execute("""CREATE TABLE IF NOT EXISTS gid(gid_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                latitude REAL, longitude REAL)""")
    c.execute("SELECT AddGeometryColumn('gid','the_geom', 4283,'POINT','XY')")
    conn.commit()

And this is the code to create the geometry

someGeometry = [(150.895,-33.961),(150.896,-33.959),(150.893,-33.960)]
for longitude,latitude in someGeometry:
    c.execute("""INSERT INTO gid(gid_id,latitude,longitude,the_geom)
                 VALUES(NULL,?,?,GeomFromText('POINT((%f %f)',4283))""" %(longitude,latitude),
                    (latitude,longitude,))

conn.commit()

I have also tried to remove the python string formatting by inserting only 1 point with specific coordinates like this

c.execute("""INSERT INTO gid(gid_id,latitude,longitude,the_geom)
                 VALUES(NULL,?,?,GeomFromText('POINT((150.895,-33.961)',4283))""",(latitude,longitude,))

Answer 1

In case anyone is trying the same, you can use geopandas with the to_file arguments to write to spatialite.

import geopandas as gpd
from shapely.geometry import Point

gdf = gpd.GeoDafaFrame([(0,150.895,-33.961,Point(150.895,-33.961)] columns=[gid_id,latitude,longitude,the_geom],geometry="the_geom")

gdf.to_file("somedatabase.sqlite", driver="SQLite", spatialite=True, layer="gid")