But generally you can just wrap separate units of work with different db_session s

Thanks that did the trick :)

Hi everybody!!!

My name is Germán. I'm from Venezuela and I love PonyORM

Hi :)

I have a question

What is it?

I can't get why this error... Reverse attribute for Voucher.oferta_id not found

Paste your models, maybe on http://gist.github.com/

Ok!

Https://pastebin.com/kvv4WY2K

Add this to Offer: voucher = Required('Voucher')

By the way, I think it's better to remove "_id" from attributes, as you are referencing an object, not an ID of an object

Oh!!! 😅 Thank a lot!!!

Hi Germán! I think you need to fix some relationship attributes. For example, in the following definition ``` class Reservation(db.Entity): ... deposit_tx = orm.Optional(str) guest_id = orm.Required(Guest) offer_id = orm.Required(Offer) ... ``` It is incorrect to name attribute `guest_id`, because in Pony its value is Giest object itself and not its id value. It is implemented using a column which keeps id value, but in Python you will get Guest object when accessing this attribute. The same is with `offer_id` attribute. So the code should look like ``` class Reservation(db.Entity): ... deposit_tx = orm.Optional(str) guest = orm.Required(Guest) offer = orm.Required(Offer) ... ``` If for some reason you want the column name which ends with _id, you can specify it using `column` option: ``` class Reservation(db.Entity): ... deposit_tx = orm.Optional(str) guest = orm.Required(Guest, column=guest_id) offer = orm.Required(Offer, column=offer_id) ... ```

Oh Great!!! Thank a lot!!!! Nice group guys. One point more to love ponyorm even more.

Each relationship attribute should have corresponding reverse attribute in another entity. If one entity Foo has attribute bar = orm.Required("Bar") then Bar entity should have something like foos = orm.Set("Bar")

By the way, Alexander is the creator of Pony, he's probably too modest to say :)

😀

😱

Alexander thank you for such great thing. I really ❤ Ponyorm

You are welcome! :)

Good morning guys!

One question. The entity relationship may be with an Optional?

Ok I saw it!

Good. Yes, it stands for 'Zero or One'. As I remember.

Yes. I found it in the docs. Thank you again.

Hello again 👋

I have to insert two billion rows into a DB so I'm trying to speed up every part of the process. I've noticed the initialization of instances is a big bottleneck. In particular cut_traceback and core.validate? Do you know how to switch off cut_traceback? And could I switch off validate as well if I'm confident in the integrity of my data?

I have to insert two billion rows into a DB so I'm trying to speed up every part of the process. I've noticed the initialization of instances is a big bottleneck. In particular cut_traceback and core.validate? Do you know how to switch off cut_traceback? And could I switch off validate as well if I'm confident in the integrity of my data?

Maybe you should not use an ORM for this process

I should not but I don't want to have to add raw sql to my code if I can just change a few config options and gain substantial speed.

https://docs.ponyorm.com/api_reference.html

Look for the insert method. On mobile so can't direct link

insert(table_name|entity, returning=None, **kwargs) Insert new rows into a table. This command bypasses the identity map cache and can be used in order to increase the performance when you need to create lots of objects and not going to read them in the same transaction. Also you can use the execute() method for this purpose. If you need to work with those objects in the same transaction it is better to create instances of entities and have Pony to save them in the database.

Thanks for the tip. It made the process 1.5 times faster but it won't be enough, looks like I'll have to go with raw sql after all.

Are you doing a lot of inserts per transaction?

200000

How much faster was raw SQL?

4 times faster than the insert method

Are you able to share the code for raw and insert? seems like a surprising difference

Sure hold on

https://gist.github.com/anonymous/8a62f9a41319d6f74094c6af371b6602 here ya go

Forgive the ugly code, it's just the profiling prototype

The difference in speed is probably because I'm able to do a single execute with psycopg2 vs 200000 with pony

How many batches of 200k did you do?

one?

A few dozens

I only profiled one

The difference in speed is probably because I'm able to do a single execute with psycopg2 vs 200000 with pony

yep seems to be the case

well, one INSERT

yeah

Analytics query on dedicated server with postgres 9.3: 47 seconds same query with postgres 10.1 on VPS (not dedicated cores) with 6 parallel workers and a lot less ram: 4 seconds

It seems like a postgres 10 upgrade could be very helpful, are there any downsides?

I have not used it yet, probably the swith to Postgres 10 should not have any downsides

By the way, it is available from Postgres instead of using the default ubuntu version: https://www.postgresql.org/download/linux/ubuntu/

hello, is it posible to sort a result by an calculation like .order_by(column1 + column2)

In [5]: select((u, u.id + 5) for u in User).order_by(2)[😏 Out[5]: [(User[1], 6), (User[2], 7), (User[3], 8), (User[4], 9)]

so make it a calculated column, then order by that column

Thanks. Great idea.

Also you can write arbitrary expressions in order_by using lambda: select(x for x in X).order_by(lambda x: x.a + x.b)

Hi guys. I have a stupid problem. But I'm googling it but I can't find an answer good for my case. I'm getting a IntegrityError: 'FOREIGN KEY constraints falided' Error. But I can see why that. Could you give any clue of when this error happen?

Hi, do you use some pre-existing tables, or created a new tables with Pony?

It is for a demo_content. So each time I delete the whole file to create it again.

Can you show a traceback?

I get the error when I try to populate the table.

pony.orm.core.TransactionIntegrityError: Object Voucher[new:15] cannot be stored in the database IntegrityError: FOREIGN KEY constraints falided

It's hard to say what is the reason for the error without looking at full traceback. Maybe you need to show models also. The error took place not in Pony itself, but inside the database, so the error message is pretty general

https://pastebin.com/UBAYR57j

https://pastebin.com/LUXZNvZb

I remember that such errors can take place in the following situation: Consider the database with two classes - Person and Phone: class Person(db.Entity): name = Required(str) phones = Set("Phone") class Phone(db.Entity): number = Required(str) person = Required(Person) In such a database when someone creates a new Phone object it is required that the corresponding Person object already exists in the database. When multiple objects created at once, Pony automatically arrange objects in a right order: p1 = Person(name='John') phone1 = Phone(person=p1, number='123-45-67') commit() In this situation, Pony inserts p1 object before phone1 object, so when phone1 object inserted into the database, it can be stored without any erors. But Pony allows to specify object id instead of object itself: phone2 = Phone(person=777, number='234-56-78') When Pony sees id value instead of object, it assumes that the object with that id value already exists inside the database. If this is not true, then the database will raise the error that you encountered.

Specifying id value instead of object is an optimization trick which can be applied if a programmer does not want load object from the database, and wants to say to Pony: "hey Pony, the object with this id is already created, I guarantee that, don't waste your time for checking this". In general it is better to specify object itself instead of specifying id

So you should not specify raw id as a value of relationship attribute without exact reason

Didn't know raw ID was possible, cool :)

So you did't use them?

Ah, ok, it was not you :)

Nope, always used User[1] etc but it is rare

The problem that I have is that an offer could be in many instances of vouchers. Maybe my code is broken when I'm trying populate using the same offer to a different voucher.

Thank you for the clue!

So you don't use id values as I described before?

I'm using an offer Object to populate voucher.