Hi Bulat, thanks for reporting, I think we need to fix it

?

That would be great!

Hi! I am new to Pony, and got stuck with MySQL. I have a table of users, and datetime field in it, and pony's mysql connector raises exception during parsing '0000-00-00 00:00:00', which is returned for NULL datetime fields lib/python2.7/site-packages/pony/orm/dbproviders/mysql.py", line 334, in str2datetime return datetime(*imap(int, s.split())) ValueError: year is out of range

Fixed. Should work now. This date will represent as None.

Good morning - Just FYI: We're using pony with SQLite for unittesting and postgresql in production. It's awesome. 10 x ? hashtag @dematic.com

Of mere curiosity: Are anyone working on implementing database triggers into db.Entity?

I'm glad you like it ?

At this moment we don't working on database triggers. But you can create them manually and use them with pony

Good morning - Just FYI: We're using pony with SQLite for unittesting and postgresql in production. It's awesome. 10 x ? hashtag @dematic.com

That means you will not use anything particular to Postgres right? e.g. JSON & JSONB

The problem with triggers is they may change data in the database and pony will not know about that, so object already loaded into memory may become out of date. It is possible in pony to mark attribute as volatile, that means it can be changed by trigger, so pony re-fetch attribute value after the object was changed

@ifindam We decided about 3 months ago to use ponyorm for all database interaction - including the database definition - which is stored in a "model.py" that is importable for all other modules.

@ifindam We decided about 3 months ago to use ponyorm for all database interaction - including the database definition - which is stored in a "model.py" that is importable for all other modules.

Cool ?

By starting in pony, we can create unittests with: from model import get_db where: db = get_db(as_test=True) leads to the loading of an sqlite db with necessary test data loaded into memory.

db = get_db() leads to loading of the postgres production defaults.

We use nosetests' general principles with: def setup(): db global db = get_db(as_test=True) def teardown(): pass # drops the db from memory all code inbetween can then run with the "in memory" db variable shared.

The test suite executes about 8,000 lines of tests in 80-100 seconds (depending on the hardware ~ i5 / i7)

That's pretty much it.

I can write a short tutorial if anyone is interested?

It think it would be cool. Maybe a blogpost

The problem with triggers is they may change data in the database and pony will not know about that, so object already loaded into memory may become out of date. It is possible in pony to mark attribute as volatile, that means it can be changed by trigger, so pony re-fetch attribute value after the object was changed

I mainly need this to hand over work from one process to another. I.e. the JobQueue model: process 1 (a webserver) stores a job in the jobqueue. process 2 needs to be notified so it can start working on the job (long running analytics)

It think it would be cool. Maybe a blogpost

+1

I can write a short tutorial if anyone is interested?

Please do. I'm interested

I could set up (yet another) webservice that receives a ping from Process 1 and then looks at the jobqueue, but I'd like the seperation of "www" and "db background" which a trigger gives.

I could set up (yet another) webservice that receives a ping from Process 1 and then looks at the jobqueue, but I'd like the seperation of "www" and "db background" which a trigger gives.

I could set up (yet another) webservice that receives a pong from Process 1 and then looks at the jobqueue, but I'd like the seperation of "www" and "db background" which a trigger gives.

It think it would be cool. Maybe a blogpost

Please, what's the work of @bonbot ?

Hi, I got distracted a few weeks ago, now finishing off my testing of the "LIMIT 2" get composite key bug fix.

With 0.7.3, I now get no LIMIT at all, where there used to be LIMIT 2

In [4]: models.ReviewProduct.get(asin='12345678', country_code='UK') SELECT "id", "asin", "country_code", "title", "image_url", "created", "first_review_scrape", "scheduled" FROM "ReviewProduct" WHERE "asin" = ? AND "country_code" = ? [u'12345678', u'UK']

this is with composite_key(asin, country_code)

is that the correct behaviour?

This is with SQLite running locally

Looks like a bug, I'll check it

My apologies for not being able to test properly after your fix

Please, what's the work of @bonbot ?

You need to ask @luckydonald I think

You need to ask @luckydonald I think

I'm here! Whassup?

Hi Lucky, some people are interested to know what is bonbot and how it works

It think it would be cool. Maybe a blogpost

I'll look into that over the next few days ??

Cool!

Can you link to the commit where the GET fixed was implemented please?

https://github.com/ponyorm/pony/commit/fcecf7a28f5c0483334a54ff31c8b25553de8bdc nevermind, got it

Sure, wait a second

Always the way, I find stuff as soon as I ask

Ok, good :)

By the way, the reason this is important to me is that with a big products table, if a user adds 1000 products, and with 1000 GETs, the inefficient queries make it quite slow

I know I could do one query but it would make the code harder to deal with

Without composite key, on live database:

app=> select * from reviewproduct where asin='12345678' and country_code='UK'; id | asin | country_code | title | image_url | created | first_review_scrape | scheduled —--+------+--------------+-------+-----------+---------+---------------------+---------— (0 rows) Time: 3.070 ms app=> select * from reviewproduct where asin='12345678' and country_code='UK' limit 1; id | asin | country_code | title | image_url | created | first_review_scrape | scheduled —--+------+--------------+-------+-----------+---------+---------------------+---------— (0 rows) Time: 0.550 ms

oh, that's because of caching, but I do remember there being a significant difference

I just tested queries with composite keys, they looks good: from pony.orm import * db = Database('sqlite', ':memory:') class X(db.Entity): a = Required(int) b = Required(int) c = Required(int) composite_key(a, b) db.generate_mapping(create_tables=True) with db_session: X(a=1, b=2, c=3) X(a=4, b=5, c=6) with db_session(sql_debug=True): x1 = X.get(a=1, b=2) x2 = X.get(b=5, c=6) The result: SELECT "id", "a", "b", "c" FROM "X" WHERE "a" = ? AND "b" = ? [1, 2] SELECT "id", "a", "b", "c" FROM "X" WHERE "b" = ? AND "c" = ? LIMIT 2 [5, 6]

Shouldn't x1 have LIMIT 1?

It should be unnecessary, because database already know that combination of a and b is unique, so at most one row should be retrieved, and the database sould take that into account when generating a query plan

Like, when you retrieve object by primary key, you don't add LIMIT 1 to the query

I can write a short tutorial if anyone is interested?

would be great!

So, in your example: select * from reviewproduct where asin='12345678' and country_code='UK'; if asin and country_code is an unique combination, you need to add UNIQUE index to the database using composite_key

Yes, I think you're right

this is the problem with leaving something for a few weeks, I forget details :)

If for some reason it is not an unique combination in general, but you are pretty sure that for this specific values it is unique, you can write query as: ReviewProduct.select() \ .filter(asin='12345678', country_code='UK).first() This query will be with LIMIT 1 because of first(). But it will not be very efficient, because database will do sequential scan in order to find the row. So, adding UNIQUE index to the database is better

Yeah, they will always be unique

By the way, the reason this is important to me is that with a big products table, if a user adds 1000 products, and with 1000 GETs, the inefficient queries make it quite slow

I'm doing inserts (currently 23,914 records) and I see that we spend 57.7% of the run time in "cut_traceback".

I think the culprit is the decorator in pony.utils.utils.py

@decorator def cut_traceback(func, *args, **kwargs): if not (pony.MODE == 'INTERACTIVE' and options.CUT_TRACEBACK): return func(*args, **kwargs) .....

How many inserts per transaction?

2000 per commit

How long is it taking?

about 2 seconds per commit (to memory)

Can you show the code?

...it's the lower loop.

for row in iterrows()...

Here's the schema:

Here's a call graph if it helps?

@decorator def cut_traceback(func, *args, **kwargs): if not (pony.MODE == 'INTERACTIVE' and options.CUT_TRACEBACK): return func(*args, **kwargs) .....

You wouldn't need to set the decorator, if pony.mode isn't interactive

You wouldn't need to set the decorator, if pony.mode isn't interactive

So you could set it once, and remove the if

Thus not adding overhead to every call

Sorry for bad mobile typing Def cut_trceback_decorator(func): Def cut_traceback(*args,**kwargs): Try: func(*args,**kwargs) #etc. # end def If interactive: return func Else: return cut_traceback

I agree that we probably can remove decorator if pony mode is not interactive. But actually, its overhead is very small. If I understand correctly, 57.7% of time was spent not in decorator, but inside a wrapped functions, that is, commit and Entity.__init__. The time that was spent in decorator itself is 251ms (0.9%)

@decorator def cut_traceback(func, *args, **kwargs): if not (pony.MODE == 'INTERACTIVE' and options.CUT_TRACEBACK): return func(*args, **kwargs) .....

File is https://github.com/ponyorm/pony/blob/orm/pony/utils/utils.py#L55

File is https://github.com/ponyorm/pony/blob/orm/pony/utils/utils.py#L55

Permanent link to the file pony/utils/utils.py mentioned. (?)

Hi everyone. Sorry if it's not the place to ask but I can not find the answer to my problem on Google. I would like to know if it's possible to have a relation one to many between two entities using two keys (both are the primary key). I've tried to use the model created by online editor and It's not working. :

class Faclin2(db.Entity): facl_facc_id = Required(int) facl_facc_serie = Required(str) facl_descripcion = Optional(str) PrimaryKey(facl_facc_id, facl_facc_serie) class Faccab2(db.Entity): facc_id = Required(int) facc_serie = Required(str) facc_fecha = Optional(date) facc_cli_id = Required(int) faclin2s = Set(Faclin2) # the problem PrimaryKey(facc_id, facc_serie)

What is the error you get?

pony.orm.core.ERDiagramError: Reverse attribute for Faccab2.faclin2s not found

Thank you in advance!

In Pony it is necessary to define both sides of relationship. If it is one-to-many, that means that each Faclin2 may have at most one Faccab2. So you need to add Required or Optional attribute to Faclin2: class Faclin2(db.Entity): ... faccab2 = Required("Faccab2") or class Faclin2(db.Entity): ... faccab2 = Optional("Faccab2")

I was about to say this but saw that Alexander was typing, and he's the expert :)

Thank you very much Alenxander and Matthew but I continue getting the same error

I made a mistake in my model. this is the right model from de online editor: class Faccab2(db.Entity): facc_id = Required(int) facc_serie = Required(str) facc_cli_id = Required(int) facc_fecha = Optional(date) faclin2s = Set('Faclin2') PrimaryKey(facc_id, facc_serie) class Faclin2(db.Entity): facl_facc_id = Required(int) facl_facc_serie = Required(str) facl_facc_descripcion = Optional(str) faccab2 = Required(Faccab2) PrimaryKey(facl_facc_id, facl_facc_serie)

The code that you posted is working without any error

I get the error: pony.orm.core.ERDiagramError: Reverse attribute for Clientes2.faccab2s not found

Sorry

This one: pony.orm.dbapiprovider.OperationalError: (1054, "Unknown column 'faclin2.facl_facc_descripcion' in 'field list'")