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.

Just Because I read is the best practice. But rather I prefer just use an int for id as you said.

It is better to use object because it is more safe

Maybe the problem is caused by some bug in Pony. It would be helpful if you can show me the code that creates objects. If it is confidential you can send it as a personal message to me

Is not confidential.

Ok, then show the code of add_voucher method

I'll paste the code here if isn't a problem. (With pastebin of course)

As well as get_offer method

Sure

I'll paste the code here if isn't a problem. (With pastebin of course)

Or you can just upload as a file in telegram

Https://pastebin.com/LeGhyfnb

Tell me If you need to run the code. You will need the db.cvs file

But it has private data. So I will have to fake it.

Ok, I'll just look at code at first

When instantiating an entity with a foreign relation, can I specify the foreign key instead of passing another entity?

Yes, I just wrote about that

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

That's great, thanks!

Is there an alternative to upgrading my Postgres 9.3 install to be able to use JSON with Pony?

Nevermind

Hello. Tell me why it does not work? a = select(p.name for p in Parts) a = a.filter(lambda p: "20" in p.name) print(a[?) AttributeError: 'str' object has no attribute 'name'

print a[ : ]

Hello!

Cause lambda argument for filter function is p.name in your case.

Filter always gets what was the X in select(X for ...

Can I make it this way? a = select(p.name for p in Parts if "20" in p.name)

If you have version of pony 0.7.3 you can use where instead of filter, it will work as you want

Ofc you can

Wait

GOOD a = select(p.name for p in Parts) a = a.where(lambda p: "20" in p.name)print(a[?)

Or not, need to check

a = select(p.name for p in Parts) a = a.where(lambda p: "20" in p.name) print(a[?) It works

Okay then.

In query q = select(x.a for x in X if ...) q = q.filter(lambda arg: ...) q = q.where(lambda p: ...) The argument arg of q.filter refers to x.a, the result of select expression (the name of argument arg may be arbitrary) The argument p of q.where refers to loop variable name p of original select generator

understandably! Thank you!

Another question Why if a = select(p.name for p in Parts) text = "12" a = a.where(lambda p: text in p.name) QUERY : FROM parts p WHERE p.name LIKE concat('%%', replace(replace(replace(%s, '!', '!!'), '%%', '!%%'), '_', '!_'), '%%') ESCAPE '!' ['12'] if a = select(p.name for p in Parts) a = a.where(lambda p: "12" in p.name) print(a[?) QUERY : FROM parts p WHERE p.name LIKE '%%12%%

why is added WHERE p.name LIKE concat('%%', replace(replace(replace(%s, '!', '!!'), '%%', '!%%'), '_', '!_'), '%%') ESCAPE '!'

Cause adding string variables right in sql query is dangerous

And it works different in pony, when pony sees constant and global variable

But tons of replace seem ugly, I agree)

anti sql injection ?

Yup

Affects the speed of the slave?

Slave?

Affects the speed of work?

Excuse me)

Because in SQL in order to check that a substring is a part of string you need to use LIKE operator. And LIKE operator has escape symbols % and _ which are treated in a special way. _ means "any symbol" and % means "any number of any symbols" If a substring is received from a column, it is possible that the substring contains these symbols. And then the result of LIKE would be incorrect. In order to prevent that, we need to replace each % to !% and each _ to !_. Then !% will be interpreted as just a single % symbol without any special meaning. The same is with _ In other words, this expression is the simplest expression which works corectly when p.name contains % or _ symbols

More actions always produces more additional time. But I think here you dont lose much. Anyway this things are important.

I understand. Thank You! You, well done)

Last question! Excuse me! I can not find it in the documentation. a = select(p.name for p in Parts) a = a.where(lambda p: "14" in p.name) a = a.where(lambda p: 14 == p.id) print(a[?) WHERE p.name LIKE '%%14%%' AND 14 = p.id How to change AND to OR?