How to implement custom algorithm of fk_name generation? Thanks.

And one more question: class FridgeServiceParameter(db.Entity): ... ValueElementListSet = ... attribute ValueElementListSet replace to valueelementlistset in the process of schema generation. How to change the generation process so that the name of the attribute is generated as is? With double quotes "ValueElementListSet"?

Only option with the indication of attribute parameter "column"?

I just saw this on Hacker News, seems relevant to pony users :) http://tatiyants.com/postgres-query-plan-visualization/

I just saw this on Hacker News, seems relevant to pony users :) http://tatiyants.com/postgres-query-plan-visualization/

This is cool!)

Also https://github.com/simon-engledew/gocmdpev

postgres, pony 0.7.2: class SalePointParameter(db.Entity): _table_ = ['core', 'SalePointParameter'] ID = PrimaryKey(int, auto=True, column='ID') Name = Required(str, 150, column='Name') ...... class SalePointParameterElementList(db.Entity): _table_ = ['core', 'SalePointParameterElementList'] ID = PrimaryKey(int, auto=True, column='ID') List = Required('SalePointParameterList', column='ListID') # ListID -> ID ..... generate sql CREATE TABLE "core"."SalePointParameterList" ( "ID" SERIAL PRIMARY KEY, "Name" VARCHAR(256) NOT NULL, ... CREATE TABLE "core"."SalePointParameterElementList" ( "ID" INTEGER NOT NULL, "ListID" INTEGER NOT NULL, Whyyyyyy...? :(

Hi Andrew, what do you mean? Your code contains many missing parts, and cannot be executed. Probably, there is some SalePointParameterList entity which Python definition is missed here. Also, reverse attributes for relations are not shown

How to implement custom algorithm of fk_name generation? Thanks.

We need to add the possibility to specify custom foreign key names. It will be great if you open an issue for that: https://github.com/ponyorm/pony/issues

We need to add the possibility to specify custom foreign key names. It will be great if you open an issue for that: https://github.com/ponyorm/pony/issues

I think he is asking about overriding the method

Problem with custom fk name is solved: replace get_default_index_name and get_default_fk_name by MethodType before call generate_mapping.

Yeah that's similar to what I'd do

Yes. Also it is possible to replace normalize_name() method which uses for all names, not only foreign keys and indexes. In 0.8 release we will change the algorithm of name cutting, the new algorithm will add unique hash to the name. This change is tied with migration, because Pony will need to upgrade existing databases to new name convention

Good news! :)

> How to change the generation process so that the name of the attribute is generated as is? With double quotes "ValueElementListSet"? You can replace get_default_column_names() method. Probably you already discover that as well

I tracing code now. With Optional/Required to null/not null same problems.

You can explicitly set nullable attribute option

hi

Does Pony ORM support Mariadb?

You can explicitly set nullable attribute option

Script and result. It may be necessary to specify reverse?

ALTER TABLE "core"."SalePointParameterElementList" ADD CONSTRAINT "fk_('core', 'salepointparameterelementlist')__distributionregio" FOREIGN KEY ("DistributionRegionElementListID") REFERENCES "core"."SalePointParameterElementList" ("ID") It seems we have a bug in get_default_fk_name method which shows itself when a table name contains a schema name

We need to fix that

Does Pony ORM support Mariadb?

It should, although we didn't test that specifically. AFAIK, most syntax should be the same as for MySQL. We have an open issue that JSON fields in Pony don't work in MariaDB.

We need to fix that

Script generated without replaced method - pure pony 0.7.2 (reinstalled): CREATE TABLE "core"."SalePointParameterElementList" ( "ID" INTEGER NOT NULL, ... but class SalePointParameterElementList(db.Entity): _table_ = ['core', 'SalePointParameterElementList'] ID = PrimaryKey(int, auto=True, column='ID') ...

This result after replace methods.

It should, although we didn't test that specifically. AFAIK, most syntax should be the same as for MySQL. We have an open issue that JSON fields in Pony don't work in MariaDB.

this is what I found exactly the same with sqlalchemy

What ways exitst to handle a OptimisticCheckError?

pony.orm.core.OptimisticCheckError: Object WebSession[UUID('c9541cfd-4458-4aba-8d0e-a768a93befd2')] was updated outside of current transaction

I realized the last_used column of that UUID in the database is set to 2017-07-25 08:02:06.213536, which is where the error started

Optimistic check error arises in the following situation: 1) db_session A loads object X and read the value a1 of attribute a 2) db_session B loads object X, updates attribute a to value a2 and commits successfully 3) db_session A continues to working. It modify attribute X.b to value b2 and try to save the object Pony issues the update query: update object X and set value of X.b to b2 if value of X.a is still v1. But the update fails, because other transaction already changed the value of X.a In order to prevent Optimistic check error you can do one of the following: 1) Make transactions fully isolated: db_session(serializable=True). This make sense if value of X.a is very important for transaction consistency 2) Lock X object by loading it using select_for_update: x = X.get_for_update(id=123). Other transactions will not be able to work with this object in parallel. 3) Mark X.a attribute as volatile if its value is not essential for transaction integrity and can be changed at any time: volatile=True 4) Turn off optimistick checks for X.a attribute: optimistic=False. This way the attribute will not be included in optimistick checks, but still may raise an error if the object was re-read from the database and the value of attribute was changed.

Probably the best option for you is to mark last_used attribute as volatile

Problem here is, I don't know which attribute was changed.

Might indeed be only that field.

Hi. How to get generated script without execute to db? Thanks.

Bug found. Great imperative programming with side effect :( if field PrimaryKey(Id) is in additional index, then on second index create DBIndex.init() modify attribute in column: for column in columns: column.is_pk = len(columns) == 1 and is_pk column.is_pk_part = bool(is_pk) column.is_unique = is_unique and len(columns) == 1 step 1: create is_pk = 'auto' and column.is_pk = 'auto' step 2: create regular index is_pk = False and column.is_pk = False and finish: infformation about PrimaryKey(ID) is dropped.

# Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] from pony.orm.core import * sql_debug(True) db = Database() class SPParamElemList(db.Entity): ID = PrimaryKey(int, auto=True) Name = Required(str, 100) IsDeleted = Required(int) composite_index(ID, Name) db.bind('postgres', user='postgres', password='superuser', host='localhost', database='core_db') db.generate_mapping(create_tables=True) generated CREATE TABLE "spparamelemlist" ( "id" INTEGER NOT NULL, "name" VARCHAR(100) NOT NULL, "isdeleted" INTEGER NOT NULL ) CREATE INDEX "idx_spparamelemlist__id_name" ON "spparamelemlist" ("id", "name")

if composite_index(ID, Name) change to composite_index(IsDeleted, Name) then: CREATE TABLE "spparamelemlist" ( "id" SERIAL PRIMARY KEY, "name" VARCHAR(100) NOT NULL, "isdeleted" INTEGER NOT NULL ) CREATE INDEX "idx_spparamelemlist__isdeleted_name" ON "spparamelemlist" ("isdeleted", "name")

How to fix?

fast fix dbschema.py DBIndex.init after for column in columns: add if hasattr(table.pk_index, 'columns') and column in table.pk_index.columns: continue

https://pastebin.com/3BMfJ1tp I don't know why does this appear even though i didn't write to db in this project

;/

I think i should create issue

You didn't write to db in this db session or in any db session?

In any

Can you reproduce the error stable?

It happens every run. But I'm not sure I can understand why it happens, so i can't reprodice it on smaller example ;/

I think, to understand the error in need to see your query (package_data.py, line 256)

Traceback shows only part of it

Well it's just ordinary select. I can show it.

It would be helpful

https://pastebin.com/2yanTqsX

But noexam_grade is child of ConfirmationDocumentLegacy

Now I need to know model definition of attributes conf_link.entrant and conf_link.right_type

right_type is just int

And entrant... is too big. I can say that noexam inside ConfirmationDocument

So entrant is an entity instance

yes

Actually I can also say that I repeat a lot of actions with db (build dict to send it as json to some service for statistics). And if i do this with a single similar piece of data it won't fail

Maybe something with caches?

What version of Pony do you use?

0.7.2

But... hm. Let me check something.

In version 0.7.2 code lines have different numbers

Yes, maybe it's master branch just before 0.7.2

I will check

I suspect it may be some old commit. I remember that some time ago we fixed similar bug, maybe it is already fixed in 0.7.2

https://pastebin.com/0KkRc6JT

Here

With right line numbers)

Now it is 0.7.2

In pony.orm.core, in line 2126 you can see: if new_dbval is not NOT_LOADED: reverse.db_set(old_dbval, NOT_LOADED, is_reverse_call=True)

That code means the following:

Pony thinks that the attr of obj has different value then before. Previously it was old_dbval and now it equal to new_dbval. Then Pony checks if application code already read previous value of attr attribute of obj instance, and if so, throws the error that you encountered. In order to understand the reason of the error, you can print the values of old_dbval and new_dbval. You can change the line 2126 to the following code: if new_dbval is not NOT_LOADED: log_orm('Value of %r attribute for %r object was changed in database. old: %r, new: %r' % (attr, obj, old_dbval, new_dbval)) reverse.db_set(old_dbval, NOT_LOADED, is_reverse_call=True) Maybe this information can help to debug the problem

Ok, I'll try

Oh my god

I uset Optional instead of Set

I think the error message could be more clear

Sure, I'll think about the error message

Thanks for help. Maybe I'll provide pr for such situation.

*pull request

Hi guys, is there a way to create the database if it does not exist? from pony.orm import * from config import * db = Database() try: db.bind(provider='mysql', host=URL, port=PORT, user=USER, passwd=PASSWORD, db=DATABASE) except OperationalError as e: # Create database if it does not exist print(e)

Hi Juan, we support it for SQLite only. For other DBMSes you typically need to connect to server under admin privileges, create a new user, set permissions, etc. Probably application code shouldn't do this, because it may be security hole to know admin login from application code

Oh, nice, it makes sense. I will create the DB manually. Thank you ❤️

yeah you usually want that as part of your migration/deploy flow

Is there a way to use TEXT or MEDIUMTEXT or there are only the ones listed here? https://docs.ponyorm.com/api_reference.html#attribute-types

You can specify sql_type option: description = Required(str, sql_type='MEDIUMTEXT')

Oh, nice, thank you again :D

Hello guys, I’m checking one issue that I face ocasionally when I query the DB (I guess it’s easier to reproduce during long periods between queries) The exception triggered is RollbackException: InterfaceError: (0, ‘’) The method uses only the orm.get / orm.select methods. I tried to workarround the issues setting @db_session(retry=3) and I still reproduce the issues. So, I would like to know which options I have to fix this issue, and which would be the neat way to do it.