SQLite on_conflict not working with ORM
Author: typicalTYLERCreated Mar 25, 2019Updated Sep 3, 2026
Labelsdocumentationsqlitesql
I've tried several different versions of this code to try to get primary key conflicts to be ignored on the table slippy_tiles but nothing seems to take when creating the schema from ORM, even though it is supposedly fixed in #4360
I've tried renaming the kwarg in the column definitions to sqlite_on_conflict_primary_key, sqlite_on_conflict, on_conflict, editing the primary key, making a new primary key constraint, nothing seems to get it to actually put the clause in the SQL or create a schema accordingly.
import time
from sqlalchemy import Column, Integer, String, ForeignKey, Float, Boolean, PrimaryKeyConstraint
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import CreateTable
Base = declarative_base()
class SearchPolygon(Base):
__tablename__ = 'search_polygons'
name = Column(String, primary_key=True)
centroid_row = Column(Float, nullable=False)
centroid_column = Column(Float, nullable=False)
centroid_zoom = Column(Integer, nullable=False)
inner_coords_calculated = Column(Boolean, nullable=False, default=False)
class SlippyTile(Base):
__tablename__ = 'slippy_tiles'
row = Column(Integer, nullable=False, primary_key=True, on_conflict_primary_key='IGNORE')
column = Column(Integer, nullable=False, primary_key=True, on_conflict_primary_key='IGNORE')
zoom = Column(Integer, nullable=False, primary_key=True, on_conflict_primary_key='IGNORE')
centroid_distance = Column(Float)
polygon_name = Column(String, ForeignKey('search_polygons.name'))
engine = create_engine('sqlite:///data/solar.db')
SlippyTile.__table__.primary_key = PrimaryKeyConstraint(SlippyTile.row.name, SlippyTile.column.name,
SlippyTile.zoom.name, on_conflict_primary_key='IGNORE')
from sqlalchemy.schema import CreateTable
print(CreateTable(SlippyTile.__table__).compile(engine))
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)Prints this schema:
CREATE TABLE slippy_tiles (
"row" INTEGER NOT NULL,
"column" INTEGER NOT NULL,
zoom INTEGER NOT NULL,
centroid_distance FLOAT,
polygon_name VARCHAR,
PRIMARY KEY ("row", "column", zoom),
FOREIGN KEY(polygon_name) REFERENCES search_polygons (name)
)Which contains no on conflict clauses
Source: sqlalchemy/sqlalchemy