Santosh
How to query in many to many related tsble
Santosh
class Country(database.Entity): country_code = orm.PrimaryKey(str) country_name = orm.Required(str, unique=True) companies = orm.Set('Company', reverse='countries') class Company(database.Entity): company_id = orm.PrimaryKey(str) company_name = orm.Required(str, unique=True) countries = orm.Set(Country, reverse='companies')
Problem statement
Assume case Company A is in Country X, Y and Company B in X, Y, Z and Company C in X
In the above case i want filter all the companies that are registered in country X, Y
Result should be company A and company B
I am unable to query with the input as a list ec: country_code = ['x','y']
Santosh
Many-to-Many entity relation has been defined as below.
Each country can be have multiple companies and each company can be in multiple countries, hence many-to-many relation has been choosen.
Matthew
In your query change “=“ to “in”
Matthew
Like:
Matthew
Lambda x: x.country_code in my_list
Santosh
query = orm.select(c for Company)
query.filter(lambda s:s.countries in ['us','jp']
Santosh
Tried like this
Santosh
I want query on company based on country
Matthew
select(c for c in Company if c.countries.country_code in ['us', 'jp’])
Santosh
I want to try with filter
I tried like
query.filter(lambda c:c.countries.country_code in ['us','jp']
Santosh
But fails
Matthew
what error message do you get?
Santosh
Santosh
Country and Company are many to many related tables
Santosh
Many-to-Many entity relation has been defined because
Each country can have multiple companies and each company can be in multiple countries
Matthew
select(c for c in Company if any(c.countries.country_code == cc for cc in ['us', jp’]))
Matthew
Maybe something like this, I’ve forgotten how I’ve handled it in the past
Santosh
How to do with filter, use case is if country is provided then filter by country else return all, hence I am query.filter
Matthew
filter(lambda c: any(c.countries.country_code == cc for cc in ['us', jp’]))
Santosh
Assertion error in sql translation of lambda
Santosh
Matthew
I think you will need to wait for the Pony authors to help
Santosh
Okay, will be eagerly waiting
Matthew
You should share your error
Luis Fernando
Hi. Does somebody know a boilerplate to create APIs with Pony?
Alexander
Hi! Give me a few minutes...
Alexander
If you need a query where the word ALL is present, like "companies which have offices in ALL countries of this list", in SQL you need to use EXISTS with double negation. As Pony is based on SQL, you need to use the same trick with Pony too
select(
company for company in Company
if not exists(
country for country in Country
if country.name in ['us', 'jp']
and country not in company.countries
)
)
In this query I used exists subquery with two not
Santosh
If you need a query where the word ALL is present, like "companies which have offices in ALL countries of this list", in SQL you need to use EXISTS with double negation. As Pony is based on SQL, you need to use the same trick with Pony too
select(
company for company in Company
if not exists(
country for country in Country
if country.name in ['us', 'jp']
and country not in company.countries
)
)
In this query I used exists subquery with two not
Will the same work with filter as well instead of select
Santosh
Or how do I do with filter
Alexander
Yes
query = Company.select()
country_list = ['us', 'jp']
query = query.filter(lambda company:
not exists(
country for country in Country
if country.name in country_list
and country not in company.countries
)
)
Santosh
Thanks a lot, great help
Santosh
If you need a query where the word ALL is present, like "companies which have offices in ALL countries of this list", in SQL you need to use EXISTS with double negation. As Pony is based on SQL, you need to use the same trick with Pony too
select(
company for company in Company
if not exists(
country for country in Country
if country.name in ['us', 'jp']
and country not in company.countries
)
)
In this query I used exists subquery with two not
How about if atleast one atleast one item present, like query list had countries=['us','jp'] I would like search with company that registered with us and ca as well, since us is in query list
Alexander
How about if atleast one atleast one item present, like query list had countries=['us','jp'] I would like search with company that registered with us and ca as well, since us is in query list
query = query.filter(lambda company:
exists(
country for country in company.countries
if country.name in country_list
)
)
Lucky
In pony order_by('random()') stopped working.
Alexander
did you import random?
Lucky
I belive this was working back in 2019
Lucky
I didn't change anything about the code.
Alexander
All names should be defined, even in strings
Lucky
Why would I need to import random? What does that have to do with the SQL?
Alexander
if you write
select(x for x in Y if f(x.id) > 0)
or
select("x for x in Y if f(x.id) > 0")
, the names Y and f should be defined somewhere.
When performing translation, Pony ignores names, it looks at objects specified by these names.
So you can do
from models import Person as Y
from math import sin as f
or
from models import Comment as Y
from math import log as f
And the result of translation will be different
The random function is not different in this case
Alexander
So if some name does not defined in current context, the expression cannot be translated
Alexander
It is not necessary to call it random inside a query, you can use a different name
from random import random as rnd
...
query = query.order_by("rnd()")
Lucky
That doesn't even make any sense
Lucky
query.order_by("random()")
and
query.order_by(random())
would be totally different, wouldn't it
Lucky
Like it would do .order_by(0.233393), wouldn't it?
Alexander
The second query should be written as
query.order_by(lambda: random())
And it is what the string-based query is parsed into
Alexander
String-based query is parsed into the same Python AST as the native Python query
Santosh
class Country(database.Entity): code = orm.PrimaryKey(str)
name = orm.Required(str, unique=True)
query=orm.select(c for c in Country)
sort=Country.name
query=query.order_by(orm.desc(sort))
Throws Typer error unsupported type DescWrapper
Santosh
Santosh
And this one is inconsistent
Alexander
Hi! It works for me. Can you show the full traceback?
Santosh
Okay
Alexander
from pony import orm
db = orm.Database('sqlite', ':memory:')
class Country(db.Entity):
code = orm.PrimaryKey(str)
name = orm.Required(str, unique=True)
db.generate_mapping(create_tables=True)
with orm.db_session(sql_debug=True):
query = orm.select(c for c in Country)
sort = Country.name
query = query.order_by(orm.desc(sort))
query.show()
SELECT "c"."code", "c"."name"
FROM "Country" "c"
ORDER BY "c"."name" DESC
code|name
----+----
RELEASE CONNECTION
Santosh
This works most of the times but not some time
Santosh
I will just capture as soon as it occurs and share
Alexander
ok
Jim
Hi, does anyone her already worked on a database sync tool with sqlite and pony ? lets say 2 clients working offline and which would need resync together via an online server.
Lucky
I did kinda a similar thing with a regular Pony->Elasticsearch export.
Jim
Using uuid.uuid4() as PrimaryKey, is is better (perf, memory) to store it as string(hex) or as int ?
Alexander
Not int, but uuid. int is too small, event bigint
Jim
ok cool
Lucky
What is the best way to copy a database from sqlite to posgres as automatically as possible?
I think I should apply the schema to both, and would then iterate through both's Entity s, and somehow iterate through all of the objects.
How can I best make sure I find the best order, regarding ordering so that relations work best?
Matthew
Have you looked into this? https://pgloader.readthedocs.io/en/latest/ref/sqlite.html
Lucky
I want to make that part of my usual pony_up migrations, so I need to have at least a python based solution
Matthew
I think that would be very hard to do. If you don’t have loads of tables it may be best to manually write a bunch of pony powered python to do it
Matthew
pgloader 2.0 is written in python btw
Alexander
I'm not sure it is easy to find a generic way to migrate database through entities, especially if entities have Required relationships.
But in some cases you can manually specify the order of classes, and migrate all instances of class A, then all instances of class B, etc. So, if an instance of class B has Required attribute with type of class A, you already have corresponding object.
Lucky
Okey. What would be the best way to stream entites without really caching them into the session?
Lucky
In that case the manual definition of how tables should be migrated actually makes sense.
Alexander
Okey. What would be the best way to stream entites without really caching them into the session?
Try to use short db_sessions, or long db_session splitted into separate transactions with commit(); rollback()
Lucky
How can I iterate best over the elemets?
Something like
i = 0
while True:
i += 1
elements = old_db.Entity.select(lambda: True).page(i, 100)
for element in elements:
old_db.Entity(**element.to_dict())
commit(); rollback()
# end while
Lucky
Is it better to do the submit in batches, after the elements loop, or can I do it in the inner loop as well?
Lucky
Also, how do I account for subtypes, say
class Post(db.Entity): and class TextPost(Post): and class LinkPost(Post):, etc?
Lucky
Uuurg, but pony's missing RegisterLater support is highly annoying
My old code is like this:
database.py:
db = Database()
class NotImportant(db.Entity):
pass
… blah blah …
from . import states
db.bind("postgres", host=POSTGRES_HOST, user=POSTGRES_USER, password=POSTGRES_PASSWORD, database=POSTGRES_DB)
db.generate_mapping(create_tables=True)
And states.py:
from .database import db
class States(db.Entity):
…
Now, using the
def register_database(db):
class NotImportant(db.Entity):
with pony_up and migrations does not work any longer, as db is not yet exposed.
And idea how I can make it work?
Lucky
Urgh, why does pony force me to write ugly code
Alexander
Urgh, why does pony force me to write ugly code
I'm not fully understand. What is register_database? Probably it should look like:
def register_database(db):
class SomeClass(db.Entity):
...
register_states(db)
or
def register_database(db):
register_base_classes(db)
register_other_classes(db)
Jim
Is there in the actual code base (or in the next with migration) anything to do things like https://www.sqlite.org/sqldiff.html ?
Lucky
pony.orm.core.TransactionIntegrityError: Object RedditChannel[new:2] cannot be stored in the database. IntegrityError: duplicate key value violates unique constraint "channel_pkey"
DETAIL: Key (id)=(2) already exists.
Which is really strange as I have
class Channel(db.Entity):
id = PrimaryKey(int, auto=True, unsigned=True)
for my posgres database