search

irtnog / lethbridge

SQLAlchemy ORM and Marshmallow schemas for Elite: Dangerous game data
GNU Affero General Public License v3.0
2 stars 0 forks source link

Error "can't compare offset-naive and offset-aware datetimes" when loading existing data #5

Open xenophonf opened 10 months ago

xenophonf commented 10 months ago

Using Lethbridge v0.2.3:

2023-12-29 16:42:00,452 - lethbridge.cli.import - DEBUG -   {"id64":670685799785,"name":"Obamumbo","coords":{...
2023-12-29 16:42:00,507 - sqlalchemy.engine.Engine - INFO - BEGIN (implicit)
2023-12-29 16:42:00,509 - sqlalchemy.engine.Engine - INFO - SELECT belt.name AS belt_name, belt.type AS belt_type, belt.mass AS belt_mass, belt."innerRadius" AS "belt_innerRadius", belt."outerRadius" AS "belt_outerRadius", belt.body_id64 AS belt_body_id64 
FROM belt 
WHERE belt.name = %(name_1)s 
 LIMIT %(param_1)s
2023-12-29 16:42:00,509 - sqlalchemy.engine.Engine - INFO - [cached since 72.4s ago] {'name_1': 'Obamumbo A Belt', 'param_1': 1}
2023-12-29 16:42:00,513 - sqlalchemy.engine.Engine - DEBUG - Col ('belt_name', 'belt_type', 'belt_mass', 'belt_innerRadius', 'belt_outerRadius', 'belt_body_id64')
2023-12-29 16:42:00,515 - sqlalchemy.engine.Engine - DEBUG - Row ('Obamumbo A Belt', 'Rocky', 17841000000000, 631780000, 1558700000, 670685799785)
2023-12-29 16:42:00,520 - sqlalchemy.engine.Engine - INFO - SELECT belt.name AS belt_name, belt.type AS belt_type, belt.mass AS belt_mass, belt."innerRadius" AS "belt_innerRadius", belt."outerRadius" AS "belt_outerRadius", belt.body_id64 AS belt_body_id64 
FROM belt 
WHERE belt.name = %(name_1)s 
 LIMIT %(param_1)s
2023-12-29 16:42:00,521 - sqlalchemy.engine.Engine - INFO - [cached since 72.42s ago] {'name_1': 'Obamumbo B Belt', 'param_1': 1}
2023-12-29 16:42:00,525 - sqlalchemy.engine.Engine - DEBUG - Col ('belt_name', 'belt_type', 'belt_mass', 'belt_innerRadius', 'belt_outerRadius', 'belt_body_id64')
2023-12-29 16:42:00,527 - sqlalchemy.engine.Engine - DEBUG - Row ('Obamumbo B Belt', 'Icy', 1010300000000000, 17486000000, 176240000000, 670685799785)
2023-12-29 16:42:00,567 - sqlalchemy.engine.Engine - INFO - SELECT faction.name AS faction_name, faction.allegiance AS faction_allegiance, faction.government AS faction_government 
FROM faction 
WHERE faction.name = %(name_1)s 
 LIMIT %(param_1)s
2023-12-29 16:42:00,567 - sqlalchemy.engine.Engine - INFO - [cached since 72.25s ago] {'name_1': 'Close Encounters Corps', 'param_1': 1}
2023-12-29 16:42:00,570 - sqlalchemy.engine.Engine - DEBUG - Col ('faction_name', 'faction_allegiance', 'faction_government')
2023-12-29 16:42:00,572 - sqlalchemy.engine.Engine - DEBUG - Row ('Close Encounters Corps', None, 'Dictatorship')
2023-12-29 16:42:00,630 - sqlalchemy.engine.Engine - INFO - SELECT faction.name AS faction_name, faction.allegiance AS faction_allegiance, faction.government AS faction_government 
FROM faction 
WHERE faction.name = %(name_1)s 
 LIMIT %(param_1)s
2023-12-29 16:42:00,631 - sqlalchemy.engine.Engine - INFO - [cached since 72.31s ago] {'name_1': 'Close Encounters Corps', 'param_1': 1}
2023-12-29 16:42:00,634 - sqlalchemy.engine.Engine - DEBUG - Col ('faction_name', 'faction_allegiance', 'faction_government')
2023-12-29 16:42:00,636 - sqlalchemy.engine.Engine - DEBUG - Row ('Close Encounters Corps', None, 'Dictatorship')
2023-12-29 16:42:00,969 - sqlalchemy.engine.Engine - INFO - SELECT station.name AS station_name, station.id AS station_id, station."updateTime" AS "station_updateTime", station."controllingFaction_id" AS "station_controllingFaction_id", station."controllingFactionState" AS "station_controllingFactionState", station."distanceToArrival" AS "station_distanceToArrival", station."primaryEconomy" AS "station_primaryEconomy", station.allegiance AS station_allegiance, station.government AS station_government, station.type AS station_type, station.latitude AS station_latitude, station.longitude AS station_longitude, station."largeLandingPads" AS "station_largeLandingPads", station."mediumLandingPads" AS "station_mediumLandingPads", station."smallLandingPads" AS "station_smallLandingPads", station.body_id64 AS station_body_id64, station.system_id64 AS station_system_id64 
FROM station 
WHERE station.id = %(id_1)s 
 LIMIT %(param_1)s
2023-12-29 16:42:00,970 - sqlalchemy.engine.Engine - INFO - [cached since 71.66s ago] {'id_1': 3896999680, 'param_1': 1}
2023-12-29 16:42:00,975 - sqlalchemy.engine.Engine - DEBUG - Col ('station_name', 'station_id', 'station_updateTime', 'station_controllingFaction_id', 'station_controllingFactionState', 'station_distanceToArrival', 'station_primaryEconomy', 'station_allegiance', 'station_government', 'station_type', 'station_latitude', 'station_longitude', 'station_largeLandingPads', 'station_mediumLandingPads', 'station_smallLandingPads', 'station_body_id64', 'station_system_id64')
2023-12-29 16:42:00,976 - sqlalchemy.engine.Engine - DEBUG - Row ('Diaw Metallurgic Site', 3896999680, datetime.datetime(2022, 12, 27, 19, 34, 38), None, None, None, None, None, None, None, Decimal('-9.057221'), Decimal('-124.812744'), None, None, None, 288231046837511529, None)
2023-12-29 16:42:00,978 - sqlalchemy.engine.Engine - INFO - ROLLBACK
2023-12-29 16:42:00,980 - lethbridge.cli.import - ERROR - can't compare offset-naive and offset-aware datetimes
xenophonf commented 5 months ago

Per the first answer to TypeError: cant compare offset-naive and offset-aware datetimes, it might be best to switch the relevant fields to the SQL TIMESTAMP type and compare values as floats.

Note the documented naive datetime conversion process.

xenophonf commented 4 months ago

See also Ensuring timestamp storage in UTC with SQLAlchemy.