feel free to hang here, note that some of the "chat" happens on libera.chat #sqlalchemy
My naming scheme is confusing but here are actual snippets from the code, I think maybe I have something misconfigured, https://gist.github.com/ianjosephwilson/ed8384661f3e3ec25164b98244ed50ee
If I comment out that album link it seems to work.
the issue is that in 1.x series it will do cascade backrefs. see https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#cascade-backrefs-behavior-deprecated-for-removal-in-2-0
i tried this
sqlalchemy.extract('upper.year', Tab_MGLBetreuer.gueltigkeit) >= jahr.year
jahr is a datetime.date object
gueltigkeit is the DATERANGE field
I am using mysql
Looks like I don't use session properly. Not sure what am I missing 🤔
When fetching existing data from the database and then updating, looks like SQL Alchemy doesn't recognize already stored objects that are part of relationship and tries to insert them again.
""" Here I generate data and merge it into the database """
for track_name in ['track-one','track-two','track-three']:
track = Track(name=track_name)
track.load_top_tags()
session.merge(track)
session.commit()
""" Clean session """
session.expunge_all()
""" Here I fetch stored tracks and update their related tag objects (doesnt work) """
for track in session.query(Track).all():
for top_tag in track.top_tags: # Load tags from database
print(f"{track.name} has top tag {top_tag.tag.name}")
track.load_top_tags()
session.merge(track)
session.commit()This results in (sqlite3.IntegrityError) UNIQUE constraint failed: tags.id error because SQLAlchemy is trying to insert data that already exists
Here is the full example with resulting output: https://colab.research.google.com/drive/18OMhYNxZGnKU4gaQ8l-ObKmVQEf50wtp?usp=sharing
5 replies
I have a table with an auto-increment integer id (primary key), and a unique-key constraint for three other columns. In my code, I am creating many instances of the mapped class and would like to insert them in the table, but only if they don't exist already. What is the best way to do this check for existence? I obviously can't check the primary key, so I could do a query for each object I want to insert to check whether an object with the unique-key values already exists -- this seems somewhat clunky. I was hoping to pre-fetch all objects from the table, then do a direct comparison (
my_instance in set(fetched_objs_from_table)), but that doesn't seem to work. Or I could try to insert each object and catch IntegrityError when the unique-key constraint fails, but that is probably bad for performance.
depending of the db you can use insert-on-conflict. I knot that postgresql support that: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT and sqlalchemy support https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#insert-on-conflict-upsert
I think sqlite has something similar
Thanks Federico, I will check that out.
What I am using now as workaround is a class decorator which adds an equality check (i.e., overriding __eq__ and __ne__ of the objects) similar to the one described here: https://stackoverflow.com/questions/39043003/comparing-sqlalchemy-object-instances-for-equality-of-attributes. The difference is my decorator takes the names of the attributes to compare as arguments, instead of checking all. (Unfortunately, at decoration time the __table_args__ are not populated yet, otherwise I could use the attributes used in the UniqueConstraint of the table for the comparison).
With this I can do:
# Too bad those are in a list and can't be put in a set
foos_existing = session.execute(sqlalchemy.select(Foo)).scalars().all()
for foo in foos_new:
# Fancy equality check happening here
if foo not in foos_existing:
session.add(foo)
session.commit()Issues:
- Unfortunately, this check is O(N), not O(1), as the objects are not hashable, so I can't put them in a
set(). - The inserts are not atomic -- if more
Foorecords get inserted to the table while this code runs, the insert might insert a record which already exists (i.e., has just been added). So I might have to wrap this in a transaction.
Any ideas to improve this overall?
Overall if the db you are using supports it I would use insert on conflict
Ok, I'm trying to do something like this with postgresql:
class ThingWithPaths(BaseModel):
...
path1 = Column(Array(Text))
path2 = Column(Array(Text))
@hybrid_property
def paths(self):
return self.path1, self.path2
@paths.expression
def paths(cls):
return postgresql.array((self.path1, self.path2))I'd hoped that that last expression would make the "fake" paths field queryable, like:
...filter(ThingWithPaths.paths == postgresql.array((postgresql.array(('one', 'two')), postgresql.array(('three', 'four')))))......which does produce the kindof right looking WHERE clause:
WHERE ARRAY[thing_with_paths.path1, thing_with_paths.path2] = ARRAY[ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]]]But upon execution, I get a non-specific DB error. Any suggestions?
Oh, my bad - my session (in REPL) was broken. The above actually did work with a fresh session.
Hi everyone! I have a question. I want to have two entities mapped to the same table with lots of columns, so that I can select different set of columns (defined in either model) based on what I need. Is it possible? (I am asking because if I do select(User.id. User.name) I don't get an object and I would like to have one).
1 reply
(that uses the old style query, but it will work the same with select)
if country in [TDCountry.america, TDCountry.us_america]:
query = query.where(
TwelveStocks.country=(TDCountry.america.value | TDCountry.us_america.value)) â– â– Operator "|" not supported for types "Literal['United States']" and "Literal['United States of America']"
)
I also tried: TwelveStocks.country.in_([TDCountry.america, TDCountry.us_america]) but that didn't work either
I had it working with pydantic and sqlalchemy before switching to sqlmodel:
I had it working with pydantic and sqlalchemy before switching to sqlmodel:
if country in [TDCountry.america, TDCountry.us_america]:
query = query.where(
or_(
stocks.c.country == TDCountry.america,
stocks.c.country == TDCountry.us_america,
)
)
huh
I don't think there was an error this whole time but pyright was telling me there was
ï™™ Cannot access member "in" for type "TDCountry" Member "in" is unknown Pyright (reportGeneralTypeIssues) [31, 34]
at the moment the typing story with sqlalchemy is still best effort, so you probably have to ignore that error or cast it explicitly
class TDCountry(str, enum.Enum):
canada = "Canada"
america = "United States"
us_america = "United States of America"
canada = "Canada"
america = "United States"
us_america = "United States of America"
sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DatatypeMismatchError'>: column "market" is of type tdmarket but expression is of type character varying
HINT: You will need to rewrite or cast the expression.
[SQL: INSERT INTO halts (date, "haltTime", ticker, "haltPrice", market, reason, "resumeTime", "resumePrice", country, watching) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: (datetime.date(2021, 12, 16), datetime.time(11, 11, 11), 'string', 0.0, <TDMarket.nasdaq: 'NASDAQ'>, 'string', None, None, <Country.canada: 'canada'>, False)]
(Background on this error at: https://sqlalche.me/e/14/f405)class TDMarket(str, enum.Enum):
nasdaq = "NASDAQ"
nyse = "NYSE"
iex = "IEX"
otc = "OTC"
cboe = "CBOE"
tsx_v = "TSX_V" # "TSX-Venture" "TSXV"
tsx = "TSX"
cnq = "CNQ"
neo = "NEO" halt = Halts(
date=halt.date,
haltTime=halt.haltTime,
ticker=halt.ticker,
haltPrice=halt.haltPrice,
market=TDMarket(halt.market),
reason=halt.reason,
resumeTime=halt.resumeTime,
resumePrice=halt.resumePrice,
country=halt.country.value,
watching=halt.watching,
)
SQLite requires IN clauses to be parenthesized AND have a '?' cardinality that matches the parameter, e.g. execute(" [...] IN (?, ?, ?)", (102, 103, 104))