Hi Grigori! At this moment, Pony cannot use binary attribute as a primary key, because, if I understand correctly, most databases don't allow it. In SQL binary values are typically stored using BLOB datatype, and BLOB column cannot be indexed. In some databases there are other binary types besides BLOB, like VARBINARY for small binary data. Maybe in the future we can allow primary key VARBINARY columns, if it is supported by the database. Right now an attempt to make binary attribute a primary key leads to obscure error, we need to fix the error text and make it less obscure Regarding Python types, Pony treat both str and unicode as the same unicode string type, for easier porting of applications to Python3: you can define attribute as str in Python2 application and it will work without any changes after porting application to Python3 For binary data, you can use buffer in Python2 and bytes in Python3. Unfortunately Python2 define bytes as an alias to str in a misguided attempt to make porting to Python3 easier. So you need to use buffer type in Python 2.7 for binary type

Alexander, thanks a lot for making it clear to me!

Sure!

following my question about writing file to disk I finally did It with a classmethod of my entity Document. @classmethod def new(cls, old_filename, content_type, stream, acte): d = Document( filename=cls.get_new_filename(content_type), content_type=content_type, acte=acte, ) try: d.write(stream) except Exception as exc: d.delete() return Exception(str(exc)) return d # and in postdocument() for f in vf: d = Document.new( old_filename=f["filename"], content_type=f["content_type"], stream=f["content"], acte=acte, ) if isinstance(d, Document): entities.append(d) else: errors.append(f"Une erreur s'est produite avec {f['filename']}")

return Exception part looks a bit strange

indeed 🙈 I initially checked isinstance(d, Exception) but I removed it. Il will change it

Also, in case when one of several documents raises an error, other documents will be committed anyway. It may be desired behavior or not, depending on application

yes it's desired behaviour, I return a "mixed" response if some fail.

Hi, I have a question regarding sequence_name attribute for PrimaryKey. I'm defining it while declaring entity: class REGION(db.Entity): REG_RECID = pny.PrimaryKey( int, sequence_name='SEQ_REGION_RECID', auto=True) REG_CODE = pny.Required(str, unique=True) But it is omitted on show(): class REGION(Entity): REG_RECID = PrimaryKey(int, auto=True) REG_CODE = Required(str, unique=True) and sequence is not being called while instance creation/commit: with pny.db_session: r2 = GC_REGION(GC_REG_CODE='5') pny.commit() pony.orm.core.TransactionIntegrityError: Object REGION[new:1] cannot be stored in the database. IntegrityError: ORA-01400: cannot insert NULL into ("TEST"."REGION"."REG_RECID") What I have missed?

with pny.db_session: r2 = REGION(REG_CODE='5') pny.commit() Sorry wrong table above )

Hi Anatolii! What database do you use?

Oracle 12

The show may omiss some elements from the original description. But I think we need to add sequence_name value for it. Thanks for poining this out! #todo In order to use the sequence the should be a corresponding trigger. Pony should create it when generate_mapping is called with create_tables=True option. It seems that for some reason the trigger was not created in your case.

Hmm. I’ll recheck trigger in database

It seems that i have set create tables to False, now all i ok. Sorry )

No problem )

Guys, this is my new telegram account. I need to create it, because I forgot password from my previous account, and cannot recover it

I confirm this from my previous account :)

Second time?

Save it to paper

No, it is the first time, I just now switched to the new account

Hi guys! I really want to use SQLite FTS3/4 with Pony. What options do I have for this? Is it possible at all? I want FTS table to be separate from my primary table, and I want to do JOINs on them.

If I understand correctly. it should be possible right now

You probably need SQLite binaries with FTS support enabled

I already got them ))

the question is: how to formulate the query?

Does your version of python have it enabled? I think that's the SQLite that pony will use

yeah I checked it

You can define "virtual" entity for FTS table just as a usual entity

For MATCH operation you can use raw SQL fragments

like:

query = 'some search string' select(obj for obj in MyFtsEntity if raw_sql('obj.somecolumn MATCH $query'))

wow! That's cool!

Thank you sir!

Sure

Another question: I created a trigger in SQLite on row delete from my main table to delete the corresponding row from FTS table. The trigger works OK if I use direct SQL statement to delete the original row, but Pony fails to .delete() the original object mumbling something about: OperationalError: SQL logic error Am I doing something wrong, or Pony does not play well with triggers on delete?

of course, there is always an option to use Pony's internal after_delete() hook, but I'd rather do it in SQLite itself.

OperationalError: SQL logic error is some SQLite error. You need to look at previous SQL command to understand the reason of the error. You can set_sql_debug(True) or use with sql_debugging: decorator

I'll try it out in a minute. But what *really* happens on calling delete() in Pony? At what moment does the actual `DELETE`is run by the database?

pony.orm.sql: INFO: SELECT "g"."rowid", "g"."type", "g"."signature", "g"."infohash", "g"."title", "g"."size", "g"."timestamp", "g"."torrent_date", "g"."tc_pointer", "g"."public_key", "g"."tags", "g"."addition_timestamp", "g"."version" FROM "MetadataGossip" "g" WHERE "g"."public_key" = ? AND "g"."type" = ? pony.orm.sql: INFO: DELETE FROM "MetadataGossip" WHERE "rowid" = ?

that was the last two entries in the log

the main table is MetadataGossip

When you call obj.delete() the object is marked for deletion. At this moment pony traverse dependent objects and call delete on them recursively. When next flush command is executed, all queued operations are sent to the database. flush can be called explicitly, or automatically executed before select query in order to be sure the select result reflects all previous operations

the code for deletion is just for md in md_list: md.delete()

I tried to force flush before and after each delete. No change.

Ok, I'll try to check it by calling SQL directly instead.

It is the first time I see "OperationalError: SQL logic error" exception

I think there is some logical problem inside the trigger

indeed, I was using some wrong syntax for triggers, which I copypasted from some moot source...

Thanks for the help!

Sure

by the way, would you recommend using database triggers, or Pony hooks for updating the state of FTS index tables in SQLite? What are the pros and cons for both approaches?

It would be very nice if Pony had an option for create_tables() to skip creating some tables. As it is now, we are forced to either create all tables through Pony, or through raw SQL.

Personally I'd use triggers for this specific task (maintaining up-to-date state of FTS tables). The benefit of trigger is it works always regardless do you change main table via Pony or with using raw SQL

you're right. But does Pony always plays safe with triggers?

When a trigger updates some column, and the object already loaded into memory within the current db_session, the object will not know about the attribute change, until you force its re-load. But for full text search it probably should not be an issue, because you typically update the main object and use FTS search in a two different db sessions

nice point

It would be very nice if Pony had an option for create_tables() to skip creating some tables. As it is now, we are forced to either create all tables through Pony, or through raw SQL.

You can create FTS tables using raw SQL and then perform db.generate_mapping(create_tables=True). Pony will create a table only if it doesn't already exists

It is possible to call db.execute before db.generate_mapping

Yeah, it worked. But I had to make a sandwich out of db.execute-generate_mapping-db.execute so triggers would see both tables...

thanks again ))

Maybe we need to add possiblility to explicitly specify trigger text in Pony so the trigger will be created automatically at the right moment

it would be even better to add support for SQLite's virtual FTS tables.

Hello! Is this a bug? this works well left_join(mr.reaction for mr in db.MoleculeReaction if mr.molecule == molecule and mr.is_product == False)[:2] SELECT DISTINCT "reaction"."id", "reaction"."date", "reaction"."user", "reaction"."special" FROM "patents"."molecule_reaction" "mr" LEFT JOIN "patents"."reaction" "reaction" ON "mr"."reaction" = "reaction"."id" WHERE "mr"."molecule" = %(p1)s AND "mr"."is_product" = false LIMIT 2 {'p1':1} [Reaction[1], Reaction[1195]] but this: q = left_join(mr.reaction for mr in db.MoleculeReaction) q.filter(lambda mr: mr.molecule == molecule and mr.is_product == False)[:2] raise error: File "<string>", line 2, in filter File "/home/stsouko/Private/bydlocoding/CGRdb/env/lib/python3.5/site-packages/pony/utils/utils.py", line 76, in cut_traceback reraise(exc_type, exc, last_pony_tb) File "/home/stsouko/Private/bydlocoding/CGRdb/env/lib/python3.5/site-packages/pony/utils/utils.py", line 87, in reraise try: raise exc.with_traceback(tb) File "/home/stsouko/Private/bydlocoding/CGRdb/env/lib/python3.5/site-packages/pony/orm/sqltranslation.py", line 1553, in getattr 'Entity %s does not have attribute %s: {EXPR}' % (entity.__name__, name)) File "/home/stsouko/Private/bydlocoding/CGRdb/env/lib/python3.5/site-packages/pony/utils/utils.py", line 100, in throw raise exc # Set "pony.options.CUT_TRACEBACK = False" to see full traceback AttributeError: Entity Reaction does not have attribute molecule: mr.molecule

sorry!

I used filter not where

HI, If EntityB inherits from entityA, is it possible to create create a new B but from the A. I tried item = A(classtype="B")but It seems not to work

Hi, I don't quite understand what do you mean

class A(db.Entity): pass class B(A): passI want to do something like : item = A(classtype="B", **data)which should give the same thing as B(**data)

hope its clearer

I don't understand why do you want this, but this should work: from pony.orm import * db = Database('sqlite', ':memory:') class A(db.Entity): foo = Required(int) class B(A): bar = Optional(int) db.generate_mapping(create_tables=True) with db_session: x = A(classtype="B", foo=10) with db_session: objects = select(x for x in A)[:] print(objects) The result is [B[1]]

If you want to specify B's attributes as well, it will generate an error: >>> with db_session: ... x = A(classtype="B", foo=10, bar=20) ... Traceback (most recent call last): File "<input>", line 2, in <module> File "C:\dev\pony\pony\orm\core.py", line 4342, in __init__ if name not in entity._adict_: throw(TypeError, 'Unknown attribute %r' % name) File "C:\dev\pony\pony\utils\utils.py", line 129, in throw raise exc TypeError: Unknown attribute 'bar' But it looks logical

What is the problem you are trying to solve?

In Python it should be very easy to specify correct class at the object creation time

if you know a class name, you can do cls = db.entities[class_name] obj = cls(**data)

ok thanks for the answer. since I have to use the attr of B this won't work. I will use model = getattr(db,classtype) instead.

Does anyone know of any Rust ORMs that have a similar design style to Pony?