Christian
I found the explicit sslmode setting for CockroachDB in the docs, but there's no mention of something similar for MySQL?
Christian
Getting error pony.orm.dbapiprovider.OperationalError: (3159, 'Connections using insecure transport are prohibited while --require_secure_transport=ON.') - Do I need to set this with the driver (pymysql in my case) or with Pony?
Found a solution: db.bind(provider='mysql', ..., ssl={"anything_here": True}) works just fine with pymysql
Steven
I have a really simple async method wrapped with @db_session and it reads a value from the db and returns it. But on return the value is None. Inside the method its the correct value. Anyone seen this behavior before?
Alexander
Do not use async calls inside db_session. Since db_session has cache that works over thread local variable it cannot be stored between different contexts.
Use small ATOMIC with db_session lines of code
Henri
Hmm... After removing the declaration of the id column when creating the test database (in setup_fuction(function)) auto-increment starts to work properly.
Realized that this was the difference between the Patient entity tests and the others which failed.
Still a bit weird...
Christian
Christian
I think this works:
for field in game.fields.select():
len(select(i.active_areas for i in field.innovations))
Jeff
whats the standard way of backing up, or exporting records from db tables?
Jim
There is the to_dict method
Lucky
whats the standard way of backing up, or exporting records from db tables?
For exporting, to_dict method could help you.
For backing up, use the preferred way for backups in your database of choice.
Volbil
@metaprogrammer hey, we I assume those messages above is left by spam bots (to pretent they are humans I guess)
Alexander
Yes, I'll delete them. Thanks
Volbil
👍
Christian
Can I do partial string matching (* wildcards) with Pony?
Christian
I'd like to shorten this: task = game.tasks.select(lambda t: t.slug in ['m_excellence_car', 'm_excellence_bike', ...])
Christian
Or could I do it the other way round and do ..(lambda t: t.slug[:13] == 'm_excellence_') ?
Christian
Which id?
Matthew
t.slug LIKE "m_excellence_%"
Matthew
I think
Anonymous
@metaprogrammer
Menu[new:1]
This is the object that
menu = Menu(thing=thing)
returns, why menu.id equals to None?
[aka print(Menu(thing=thing).id)]
Anonymous
my id is id = PrimaryKey(int, auto=True)
Anonymous
Alexander
Because autoincrement id becomes known when the object is stored in the database (with INSERT), and this object was not stored yet. If you perform flush() you should see id value
Anonymous
Ah, ok.
is there an argument which can be passed to db_session in order to perform that automatically
Alexander
No, but you can add flush() call at any moment
Anonymous
👌
Anonymous
seems like that
to_dict performs a flush(), doesn't it?
Alexander
yes, it actually performs flush
Anonymous
👍
Anonymous
Thanks!
Anonymous
Is there a contributing document? I'd like to make a pull request, and it seems the conventions in Pony are quite different than other projects I'm familiar with.
Anonymous
yup, just did. For future contributors, it helps to explain how to run the tests, and which branch to make the PR against. "orm" is an unusual trunk branch.
Alexander
Hi Ned! Pony doesn't have a contributing document, we should create it.
Thank you for the pull request, I'll accept it.
Anonymous
nice! 🙂
Jeff
with PonyORM, is there a need to use something like albemic for database migrations?
Jeff
or is there a built in way to handle that stuff (even though not explicitly mentioned)
Ben
Hi! A quick question I couldnt find an answer in the doc. When I use optimistic=False, is it expected that I still get "phantom object appeared in collection" errors? Is there any mode that can avoid those? I don't care about the data changing in that case
Ben
Alternatively, when processing pages of objects in the same db_session, if one of the object throws the error, can I just catch it, commit what already exists and then get that object again to reprocess it? or with the data computed from the previous object in the page be lost?
Ben
Would it work if I used allowed_exceptions=[UnrepeatableReadError]
Henri
I want to use full text search with postgres and create a separate tsvector column which joins several columns.
Something like from the docs:
ALTER TABLE pgweb
ADD COLUMN textsearchable_index_col tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
Where and how is the best way to do it and how to add the resulting column to the entity?
Inside the entity declaration with a @property and db.execute()?
And also how and where to create the GIN index? Also in the entity with db.execute?
Are there any examples?
Max
Hi, could we expect v0.8 in near 6 month please?
Alexander
Hi, not sure about release date, but next incoming version is 0.9
Max
kk, thx
Christian
Hi, not sure about release date, but next incoming version is 0.9
What about 0.8? Are you trying to be sneaky and get around migrations? :D
Alexander
No, we found out that migrations implementation is not that great in 0.8, so we created 0.9 with new one.
It still has some issues but works for easy cases. It is available on github as branch migrations_dev.
But we do not declare it like working version.
Alexander
I personally use 0.9-dev for several projects and it works fine as pony itself and can create some migrations and even manage it.
But for managing i still use flyway (migration management system).
Lucky
Christian
I personally use 0.9-dev for several projects and it works fine as pony itself and can create some migrations and even manage it.
But for managing i still use flyway (migration management system).
Thanks for the explanation, I saw the different migration branches but did not know. So we're skipping 0.8 entirely?
Alexander
I think yes
Ben
Hi all! Any idea how I can ignore UnrepeatableReadError issues in requests and just keep going? I have a case with a list that keeps changing and I want to iterate through all elements and don't care if I miss the new ones that were added.
Alexander
Hi Ben! You have two ways to solve this problem. Consider the following example:
from pony.orm import *
db = Database('sqlite', ':memory:')
class Collection(db.Entity):
id = PrimaryKey(int)
name = Required(str)
items = Set(lambda: Item)
class Item(db.Entity):
id = PrimaryKey(int)
name = Required(str)
collection = Required(lambda: Collection)
sql_debug(True)
db.generate_mapping(create_tables=True)
Alexander
We have some items which belong to collections. Let's say we have this items:
with db_session:
c1 = Collection(id=1, name='Collection1')
i1 = Item(id=1, name='Item1', collection=c1)
i2 = Item(id=2, name='Item2', collection=c1)
c2 = Collection(id=2, name='Collection2')
i3 = Item(id=3, name='Item3', collection=c2)
i4 = Item(id=4, name='Item4', collection=c2)
Alexander
Now I want to emulate the situation when UnrepeatableReadError arises. Usually, it happens when two concurrent transactions modify and access the same object (or the same collection). But to simplify the example, I can emulate it with a single transaction.
I can read the entire collection from the database. At that moment, Pony marks collection as fully loaded from the database and will raise an error if a new item suddenly appears in the database later in this transaction.
Pony cannot track raw SQL queries that I execute directly to the database. If I add a new item using a raw SQL query, it will be as surprising to the current transaction as an item added by the concurrent transaction.
Alexander
So, the next code will raise the UnrepeatableReadError exception:
with db_session:
c1 = Collection[1]
for item in c1.items:
print(item.name)
db.execute("INSERT INTO Item(id, name, collection) VALUES(5, 'Item5', 1)")
i5 = Item[5]
Alexander
When I load a new Item5 from the database, Pony sees that it actually belongs to Collection1, but Collection1 was fully loaded just a moment before, so we have a database inconsistency
Alexander
If you actually don't care about the database consistency for this collection, you have two options. The first option is to mark collection attribute as volatile:
class Collection(db.Entity):
id = PrimaryKey(int)
name = Required(str)
items = Set(lambda: Item, volatile=True)
Alexander
The Pony will ignore this problem, as volatile means that the attribute value can change in the database at any time (by trigger or by some concurrent transaction) and it is an expected situation
Alexander
The second way is suitable if most of the time you still want to have consistency check, but for some specific transaction you want to skip it
Alexander
In that case you just need to avoid explicit loading of entire collection with all items. As Pony will not be sure that all collection was loaded before with all items, it will not raise the error.
Alexander
In that case, instead of
with db_session:
c1 = Collection[1]
for item in c1.items:
print(item.name)
db.execute("INSERT INTO Item(id, name, collection) VALUES(5, 'Item5', 1)")
i5 = Item[5]
You can write:
with db_session:
c1 = Collection[1]
for item in Item.select(lambda item: item.collection == c1):
print(item.name)
db.execute("INSERT INTO Item(id, name, collection) VALUES(5, 'Item5', 1)")
i5 = Item[5]
Alexander
So instead of c1.items I use query which loads the same objects, but without explicitly loading the entire c1.items collection. Then Pony will not know that the collection is fully loaded and will not raise the error
Alexander
I want to use full text search with postgres and create a separate tsvector column which joins several columns.
Something like from the docs:
ALTER TABLE pgweb
ADD COLUMN textsearchable_index_col tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
Where and how is the best way to do it and how to add the resulting column to the entity?
Inside the entity declaration with a @property and db.execute()?
And also how and where to create the GIN index? Also in the entity with db.execute?
Are there any examples?
Hi Henri!
You can add column and GIN index manually by issuing manual query in PgAdmin. If it is necessary to do it repeatedly, you can write a database migration that includes these SQL commands. For migrations, at this moment, I recommend using FlywayDB: https://flywaydb.org/
Pony will not know about this new column. It should not cause any problems, as the column content is generated automatically.
To use this column in the query, you can define a property that contains a raw SQL query fragment:
class MyEntity(db.Entity):
...
@property
def match(self, query_string):
return raw_sql("textsearchable_index_col @@ to_tsquery($query_string)")
Then you would be able to write queries like
s = "foo bar"
query = select(x for x in MyEntity if x.match(s))
Ben
Hi Ben! You have two ways to solve this problem. Consider the following example:
from pony.orm import *
db = Database('sqlite', ':memory:')
class Collection(db.Entity):
id = PrimaryKey(int)
name = Required(str)
items = Set(lambda: Item)
class Item(db.Entity):
id = PrimaryKey(int)
name = Required(str)
collection = Required(lambda: Collection)
sql_debug(True)
db.generate_mapping(create_tables=True)
Thanks a lot for that!! This is very helpful. So volatile in this case is stronger than just using optimistic=False. I thought those were equivalent in a way.
Alexander
Currently, optimistic is responsible for whether or not Pony should add optimistic checks to UPDATE or DELETE queries, and volatile answers the question should Pony raise an exception or not if the attribute value in the database suddenly changed.
Ben
aaah I see
Ben
but there is no way to set "volatile" for a specific query?
Alexander
At this moment no. For collections you can avoid triggering the exception by rewriting queries a bit, as I described in second part of the answer.
Ben
Yup, the issue is that I dont think I can iterate through the loop without having something added to the collection
Ben
I have like 500k entities to iterate through with new entities added every few seconds
Ben
Could I monkeypatch the objects just for that query?
Ben
it's running in a different worker
Omer
Hello Pony Fam, need help. I’m getting Binding Error: Database object was already bound to mySql. Any solution. New to pony orm
Omer
Got it, my bad
Anonymous
Anonymous
my goal is:
each product can be in few menus.
each menu can contain many products.
hence many-to-many
Volbil
You can just pass menu object/s
Volbil
No need to pass ids
Anonymous
that's my issue, i don't want to do this
menus=[Menu[n] for n in menus]
in order to get each menu by its ID
Anonymous
i prefer to pass IDs list since that's what i got