Hi. I have a 500MB csv file to load to databse via pony. I thought doing a commit every 1000 entries to not overload the RAM. does somebody has any experience of it ?

db_session(strict=True)

strict (bool) – when True the cache will be cleared on exiting the db_session. If you’ll try to access an object after the session is over, you’ll get the pony.orm.core.DatabaseSessionIsOver exception. Normally Pony strongly advises that you work with entity objects only within the db_session. But some Pony users want to access extracted objects in read-only mode even after the db_session is over. In order to provide this feature, by default, Pony doesn’t purge cache on exiting from the db_session. This might be handy, but in the same time, this can require more memory for keeping all objects extracted from the database in cache.

and keep using new db_sessions every x records

that has worked for me in the past

Commit does not discard previous objects from the memory. As Matthew said, you need to use separate db_session(strict=True) for each chunk of 1000 objects. You can also use single db_session(strict=True), but perform commit(); rollback() instead of just commit(). It will force discarding previously created objects from the memory

thanks for answer. I stored my 1.800.000 lines (47 colums each) without crashing my system !!

:)

does chunk size to 10,100,1000 or 10000 change things about speed process ?

I think it is better to have the size of chunk at least 100, and probably 1000, because commit operation is slow. The difference between 1000 and 10000 is probably not so big

Hey guys, what do you think about a on/off toggle for being private directly inside the diagram listing, or is that only something I encounter as I kept versions of diagrams by duplicating?

Hi Luckydonald! Thanks for the suggestion. We'll think about it. It is indeed looks more convenient to have on/off toggle right here. Typically users should not experience that problem, because, if original diagram is marked as private, the cloned diagram will be private too

Hi! I've stumbled upon another problem. The following code results in an error: with db_session: add = chant.list_channel(my_channel_id) my_channel = chant.get_channel(my_channel_id) if my_channel: # Channel already exists, going to update it my_channel.tags = tags my_channel.title = title remove = MetadataGossip.select(lambda g: g.type == MD_DELETE and g.public_key==buffer(my_channel_id))[:] chant.update_channel(key, my_channel, channels_dir, add_list=add, remove_list=remove) else: # Channel does not exists, have to create it chant.create_channel(key, title, channels_seeding_dir, add_list=add, tags=tags) The error is: ERROR 1531743301.92 rest_manager:53 (RESTRequest) [Failure instance: Traceback: <class 'pony.orm.dbapiprovider.OperationalError'>: no such column: nem.rowid /home/vader/.local/lib/python2.7/site-packages/twisted/web/http.py:1654:dataReceived /home/vader/.local/lib/python2.7/site-packages/twisted/web/http.py:2070:_finishRequestBody /home/vader/.local/lib/python2.7/site-packages/twisted/web/http.py:2145:allContentReceived /home/vader/.local/lib/python2.7/site-packages/twisted/web/http.py:890:requestReceived --- <exception caught here> --- /home/vader/.local/lib/python2.7/site-packages/twisted/web/server.py:197:process /home/vader/.local/lib/python2.7/site-packages/twisted/web/server.py:257:render /home/vader/.local/lib/python2.7/site-packages/twisted/web/resource.py:250:render /home/vader/src/TRIBLER/tribler_ichorid/Tribler/Core/Modules/restapi/channels/my_channel_endpoint.py:107:render_POST <auto generated wrapper of __getitem__() function>:2:__getitem__ /home/vader/.local/lib/python2.7/site-packages/pony/utils/utils.py:58:cut_traceback /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:5694:__getitem__ /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:5340:_fetch /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:1651:prepare_connection_for_query_execution /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:1736:flush /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:5020:_save_ /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:4929:_save_updated_ /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:820:_exec_sql /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:1625:reconnect /home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py:818:_exec_sql <auto generated wrapper of execute() function>:2:execute /home/vader/.local/lib/python2.7/site-packages/pony/orm/dbapiprovider.py:61:wrap_dbapi_exceptions ] Traceback (most recent call last): File "/home/vader/.local/lib/python2.7/site-packages/twisted/web/server.py", line 197, in process self.render(resrc) File "/home/vader/.local/lib/python2.7/site-packages/twisted/web/server.py", line 257, in render body = resrc.render(self) File "/home/vader/.local/lib/python2.7/site-packages/twisted/web/resource.py", line 250, in render return m(request) File "/home/vader/src/TRIBLER/tribler_ichorid/Tribler/Core/Modules/restapi/channels/my_channel_endpoint.py", line 107, in render_POST remove = MetadataGossip.select(lambda g: g.type == MD_DELETE and g.public_key==buffer(my_channel_id))[:] File "<auto generated wrapper of __getitem__() function>", line 2, in __getitem__ File "/home/vader/.local/lib/python2.7/site-packages/pony/utils/utils.py", line 58, in cut_traceback return func(*args, **kwargs) File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 5694, in __getitem__ if not start: return query._fetch() File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 5340, in _fetch cache.prepare_connection_for_query_execution() # may clear cache.query_results File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 1651, in prepare_connection_for_query_execution

if not cache.noflush_counter and cache.modified: cache.flush() File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 1736, in flush if obj is not None: obj._save_() File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 5020, in _save_ elif status == 'modified': obj._save_updated_() File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 4929, in _save_updated_ cursor = database._exec_sql(sql, arguments, start_transaction=True) File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 820, in _exec_sql connection = cache.reconnect(e) File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 1625, in reconnect if not provider.should_reconnect(exc): reraise(*sys.exc_info()) File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/core.py", line 818, in _exec_sql try: new_id = provider.execute(cursor, sql, arguments, returning_id) File "<auto generated wrapper of execute() function>", line 2, in execute File "/home/vader/.local/lib/python2.7/site-packages/pony/orm/dbapiprovider.py", line 61, in wrap_dbapi_exceptions raise OperationalError(e) OperationalError: no such column: nem.rowid Funny thing is, if I put select before assignments, like this: remove = MetadataGossip.select(lambda g: g.type == MD_DELETE and g.public_key==buffer(my_channel_id))[:] my_channel.tags = tags my_channel.title = title the error goes away!

Alexander, could you please comment on how to debug/avoid this kind of problems? Thank you.

Hi Grigori, wait a second...

What database server do you use?

sqlite

As I understand the traceback, there is some entity which instances are stored in a nem table, and the table does not have rowid column. Pony assumes that the column should present in the table, but apparently this is not the case. There is an instance of Nem entity (or whatever it is called), which is updated in memory (probably, inside a function list_channel or get_channel), but not saved to the database yet. When you are trying to execute a query remove = MetadataGossip.select(...) Pony sees that there are unsaved Nem object and tries to save it to the database, because sometimes it may be important in order to get correct query result. But the UPDATE query for nem table generates an error, because rowid column is not found. You need to check, why rowid column is missed from the nem table. Maybe, for some reason, you create the nem table specifying the WITHOUT ROWID option, or something like that

the problem is, there is no nem table...

oops

it was, in the triggered procedures

a mistyp

sorry for disturbing you on such a trivial point ))

No problem

fell like

Hey guys, I hope it's okay to shoot a short question here: I was wondering if Pony provides some validity-checking mechanism at instance creation time. Something like the py_check parameter, but taking into consideration multiple attributes. I guess I could also use the before_insert / before_update hooks, but I was thinking maybe there is some mechanism, that can intercept at an earlier point. Or should I try to override the __init__ method (and call super())? Maybe someone came across this before, any answers are appreciated.

Hi Johannes. I think you need to override __init__ method and call super()

Thanks a lot, I guess I'll try this (this option came to my mind while formulating the question, therefore I didn't try it before...)

Or you can use something like marshmallow or cerberus between your datas input and pony to validate data

I worked with marshmallow (and SQLAlchemy) before - but somehow it was an unsatisfying experience, having to define everything twice. Also the checks are not too elaborate, therefore I think I could benefit from not introducing that extra layer of abstraction.

Cerberus is great!

I'll give it a shot. Thanks for all the answers.

Look what I found in the results of the december survey: https://www.jetbrains.com/research/python-developers-survey-2017/

That's cool )

@luckydonald I call the police

lol

Alexander, what happens if I change the value of the discriminator column for some entry in Pony, in case of class inheritance? For example, let's say there are Alice the Professor and Bob the Student, and both Professor and Student classes inherit from the base class Person. Say, I want to change Bob to become a Professor, and to do that I change the discriminator of his entry as a Person from Student to Professor. Will Pony manage it? Thanks.

Grigori, in principle it is possible to add a way to change object class to Pony. Something like bob.change_class_to(Professor, degree='Endowed Professor', faculty=Faculty[1]) But right now Pony assumes that objects don't change their classes, and object discriminator value is immutable. But you can change discriminator value by executing raw SQL query. In the next db_session Pony will see updated object as belonging to a new type. from pony.orm import * db = Database('sqlite', ':memory:') class Person(db.Entity): name = Required(str) class Professor(Person): degree = Required(str) class Student(Person): group = Required(int) db.generate_mapping(create_tables=True) with db_session: x = Student(id=1, name='John Smith', group=1234) with db_session: db.execute("update Person set classtype='Professor' where id = 1") with db_session: x = Professor[1] C:\Dev\pony\pony\orm\core.py:2225: DatabaseContainsIncorrectEmptyValue: Database contains NULL for required attribute Professor.degree DatabaseContainsIncorrectEmptyValue) x.degree = 'Assistant Professor' There are several different ways to implement inheritance in relational database. In Pony all inherited classes shares the same table. When subclass has required attribute, the column is actually declared as nullable. This is necessary, because for objects which belongs to different subclasses that column is missing, even if it required for that specific subclass. If you change discriminator value using raw SQL query, it is better to set all "required" attributes of new class at the same time, or Pony will generate warning upon loading an object in the next db_session. The columns of previous subclass, such as student.group will be ignored (not loaded) when the object with changed discriminator value is loaded. But the values are still be in the database. It is better to clear them in the same raw SQL query where you change the discriminator value, but this is not absolutely necessary

hi guys, do you see a way to make query ignoring the accents where select(p for p in Plob if p.name == "med")whould match "med", "méd", "mèd", "mêd". The other way is quite simple since I only have de to replace every accentued character with non accentued character before making query. But in this way I have to make a large amount of query and more if there 2 accentued chars in the word thanks

What database do you use?

pg or sqlite

In PostgreSQL there is an unaccent extension, which provides unaccent function. You need to execute create extension unaccent in order to use it. Then you can write raw SQL query: x = "med" select(p for p in Plob if raq_sql("unaccent(p.name)") == raw_sql("unaccent($x)") In SQLite you can write such function in Python and register it in SQLite But in general it may be easier and more efficient to have two distinct attributes name and unaccent_name. This way you can add index to unaccent_name and make search really fast

thank you alexander. I think I'will go with the unaccent attribute

Hi, How could it happen (debug on )? select(p.libelle_commune_coord_structure for p in Praticien if p.libelle_commune_coord_structure.startswith('PARIS'))[:] SELECT DISTINCT "p"."libelle_commune_coord_structure" FROM "Praticien" "p" WHERE "p"."libelle_commune_coord_structure" LIKE 'PARIS%'sqlite seems to be case insensitive. not same thing with pg. is that a pony or a sqlite issue ?

From this query it is not evident that the query has any problem with case sensitivity. Everything looks good

What is the query result?

sorry Out[8]: ['Paris 15e Arrondissement', 'Paris']

It seems you are right. This is from SQLite documentation: https://www.sqlite.org/pragma.html#pragma_case_sensitive_like PRAGMA case_sensitive_like = boolean; The default behavior of the LIKE operator is to ignore case for ASCII characters. Hence, by default 'a' LIKE 'A' is true. The case_sensitive_like pragma installs a new application-defined LIKE function that is either case sensitive or insensitive depending on the value of the case_sensitive_like pragma. When case_sensitive_like is disabled, the default LIKE behavior is expressed. When case_sensitive_like is enabled, case becomes significant. So, for example, 'a' LIKE 'A' is false but 'a' LIKE 'a' is still true.

Probably we need to add command PRAGMA case_sensitive_like = OFF when connecting to SQLite

you mean 'ON' ? I understand the default behavior is OFF( = insensitive)

yes, I mistyped

PRAGMA case_sensitive_like = ON

i there a way to add it with the bind command or anything else ?

Right now you can do it right before executing the query which uses LIKE: db.execute('PRAGMA case_sensitive_like = OFF') We will include the fix in the nearest release, so it will happens automatically on connecting to database

Ok cool thanks

db.execute('PRAGMA case_sensitive_like = OFF') Out[11]: <sqlite3.Cursor at 0x7f0c53e92dc0> In [12]: select(p.libelle_commune_coord_structure for p in Praticien if p.libelle_commune_coord_structure.startswith('paris'))[:] Out[12]: ['Paris 15e Arrondissement', 'Paris']nothing changed ? should I reuse the cursor ?

You should use ON, not OFF

😂😂😂🙈🙈 it's okayn ow

how do you debug a pony command that seams to be locking up

horse

Adam, you can do set_sql_debug(True) and see what queries were executed last

ty .. it wasnt locked .. it was just slow

Hi I'm having trouble to make coverage.py work with pony. I put an issue here : https://github.com/nedbat/coveragepy/issues/685 Does someone hase same issues ?

In some sense it is correct that lambda or generator passed to select is not executed. Pony just analyzes its AST to build an equivalent SQL query. Probably there needs some cooperation between Pony and coverage.py to not report this code as not executed

Guys, we just released PonyORM 0.7.4 https://github.com/ponyorm/pony/releases/tag/0.7.4 Blog post will be written soon. The release includes a huge change of internal mechanics. Now you can do the following: 1) declare method or property on Entity which will be translated into SQL when called inside select(...) like: class Student(db.Entity): ... def full_name(self): return self.first_name + ' ' + last_name @property def is_good_student(self): return self.gpa >= 4 query = select(s.full_name() for s in Student if s.is_good_student)) 2) Use query as a source for another query: query = select(s for s in Student if s.gpa > 3) query2 = select(s.full_name for s in query if s.first_name.startswith('J')) Other features: - support of Python 3.7 and PyPy was added - group_concat() aggregate function for joining of a string values - pony.flask package for basic integration with Flask and Flask-Login

:D

looks great

Cool!!! hybrid property is very nice. You finally delayed the migration tool ?

Unfortunately yes, but now we will concentrate on it

ok so good luke as it seems to be a real mess

Greatest news! Thanks 👍

Forgot to mention, it is not necessary for function to be a method of property of an instance. Any one-line function will suffice: def has_equal_name(a, b): return a.name == b.name select((p1, p2) for p1 in Person for p2 in Person if p1 != p2 and has_equal_name(p1, p2))

Wow! Cool update.

Sir Alexander , you definitely rock!