It seems that all ORs are present, they are in the top part of the query

Still for some reason query_text == st.sticker.file_id doen't seem to work. Maybe I am doing something wrong

It's here: OR %(p4)s = "sticker"."file_id"

What is that LIKE ('%%' || replace(replace(replace(%(p1)s, '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' doing?

In SQL, LIKE has special symbols _ (which means "any symbol") and % (which means "any number of any symbols"). It is possible that the param value contains these symbols. When we translate Python string in operator, we need to suppress the special meaning of that symbols and treat them as an ordinary symbols

And so we replace _ to !_ and % to !% and use ! as an escape symbol which denotes that the following symbol is just a usual symbol without any special meaning

I forgot the .lower() thats why my queries didn't return anything.

I noticed, this will only return stuff when there are st.sticker.tags. How can I improve the query that e.g. st.sticker.sticker_pack.title.lower() can trigger when there aren't any st.sticker.tags (s_t)?

pairs = orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage for s_t in st.sticker.tags for p_t in st.sticker.sticker_pack.tags if ( query_text_lower in s_t.string.lower() or query_text_lower in st.sticker.sticker_pack.url.lower() or query_text_lower in st.sticker.sticker_pack.title.lower() or query_text_lower in p_t.string.lower() or query_text == st.sticker.emoji or query_text == st.sticker.file_id ) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") and not orm.exists(t for t in st.sticker.sticker_pack.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset)

I noticed, this will only return stuff when there are st.sticker.tags. How can I improve the query that e.g. st.sticker.sticker_pack.title.lower() can trigger when there aren't any st.sticker.tags (s_t)?

Basically make the part with the for s_t and p_t optional I think the if will not be reached if there are no st.sticker.tags

i think that's a limit of nesting your comprehensions like that

since you only have a single if case for the bulk of it, rather than have them separately based on your comprehensions

Yes, I think so, too. But how would I seperate them?

are you sure that syntax is correct?

are you sure that syntax is correct?

Yes, It does not throw errors and currently does return stuff, if both for loop are non-empty

There are two approaches: 1) Refactor the query to move s_t and p_t inside subqueries: select(... for st in StickerMessage if exists(s_t for s_t in st.sticker.tags if ...) or exists(p_t for p_t in st.sticker.sticker_pack.tags if ...) or ... ) 2) Use left_join(...) instead of select(...)

I'll try 1st. Thanks

Let's see If I can get it running

Yep. pairs = orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if ( query_text == st.sticker.emoji or query_text == st.sticker.file_id or query_text_lower in st.sticker.sticker_pack.url.lower() or query_text_lower in st.sticker.sticker_pack.title.lower() or orm.exists(s_t for s_t in st.sticker.tags if query_text_lower in s_t.string.lower()) or orm.exists(p_t for p_t in st.sticker.sticker_pack.tags if query_text_lower in p_t.string.lower()) ) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") and not orm.exists(t for t in st.sticker.sticker_pack.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset) Seems working fine

Offtopic: are you sure you want to find tags which string contains query_text, but not equal to it?

Yes, actually. Until I had a look into something better suited like Full text search [1], Levenshtein distance [2] or something similar... 1: https://www.postgresql.org/docs/9.6/static/textsearch.html 2: https://en.wikipedia.org/wiki/Levenshtein_distance

Offtopic: are you sure you want to find tags which string contains query_text, but not equal to it?

Because that is the inline search query for @StickerTagBot.

Hello everyone

How do I add a new column to the table by ORM without deleting the table before?

deleting = dropping

Hi @Chackero, we are making migration tool for that, it is almost done

Hello! I try to use .to_dict() method on class Conditions(db.Entity): id = PrimaryKey(int, auto=True) citation = Optional(LongStr) comment = Optional(str) conditions = Optional(str) description = Optional(LongStr) pressure = Optional(str) product_yield = Optional(str) steps = Optional(str) temperature = Optional(str) time = Optional(str) raw_medias = Set('RawMedias') reaction = Required('Reactions') I expected to see all optional fields. but I get only part of them.

LongStr attribute values may be very big, and because of that they are declared as lazy by default. Lazy attributes are not retrieved from the database automatically without explicir request, and they are not automatically returned by to_dict. You can do one of the following: 1) Add with_lazy=True option: obj.to_dict(with_lazy=True) 2) Specify attribue as a not lazy one: description = Optional(LongStr, lazy=False)

Thank you!

Hi @Chackero, we are making migration tool for that, it is almost done

OK. I'll wait. Thank you.

Hi, I have to manage MySQL connection timeout (on pythonanywhere.com they set a 5-minute timeout: https://help.pythonanywhere.com/pages/UsingMySQL/), so I'm having errors like: 'pony.orm.core.RollbackException: OperationalError: 2006 MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))' 'pony.orm.core.RollbackException: OperationalError: 2013 Lost connection to MySQL server during query' SQLAlchemy has pool_recycle; is there any equivalent in Pony ORM? I'm using Flask.

Hi, Pony ORM should be able to handle this error automatically. Can you provide a more detailed traceback?

2016-12-21 13:29:42,485 :Exception on /documenti [GET] Traceback (most recent call last): File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/flask/app.py", line 1988, in wsgi_app response = self.full_dispatch_request() File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/flask/app.py", line 1641, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/flask/app.py", line 1544, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise raise value File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request rv = self.dispatch_request() File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "<string>", line 2, in documenti File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 416, in new_func finally: db_session.__exit__(exc_type, exc, tb) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 390, in __exit__ else: rollback() File "<string>", line 2, in rollback File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/utils/utils.py", line 58, in cut_traceback return func(*args, **kwargs) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 319, in rollback transact_reraise(RollbackException, exceptions) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 276, in transact_reraise reraise(exc_class, new_exc, tb) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/utils/utils.py", line 85, in reraise try: raise exc.with_traceback(tb) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 316, in rollback try: cache.rollback() File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 1552, in rollback cache.close(rollback=True) File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/core.py", line 1566, in close try: provider.rollback(connection, cache) File "<string>", line 2, in rollback File "/home/dadecar/.virtualenvs/qsorg/lib/python3.5/site-packages/pony/orm/dbapiprovider.py", line 53, in wrap_dbapi_exceptions except dbapi_module.OperationalError as e: raise OperationalError(e) pony.orm.core.RollbackException: OperationalError: 2006 MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))

I think this is a bug. Pony knows about the error 2006 "MySQL server has gone away" and should reconnect automatically without the necessity to specify any timeout. It is not clear to me why this is not happened here. I hope I will be able to understand the reason and fix the bug tomorrow.

Thanks! FYI I checked and the traceback for OperationalError: 2013 is exactly the same as the one for 2006.

Do you have some long-running transactions?

Are there any contribution guidelines for the project?

Are there any contribution guidelines for the project?

What kind of functionality you'd like to contribute?

I have a student (< 6months programming experience) who wanted to get started contributing to OSS, and he made a PR to add some docstrings

I've never contributed to OSS and want to be as helpful as I can be

I would say no. I also noticed that I have no problems on my local machine, and on server log I found these entries in correspondence of python errors (same time): SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request...

How can I put indexes on fields? Edit: Found it index=True. I noticed, the online editor has no such options. Could be handy. Also the page about index=True and composite_index(a, b) should link each other: https://docs.ponyorm.com/api_reference.html?highlight=index#cmdoption-arg-index and https://docs.ponyorm.com/entities.html?highlight=index#composite-indexes

Having url = PrimaryKey(str, index=True) Raises pony.orm.core.DBSchemaError: Index for column 'url' already exists Is that intentional?

How can I put indexes on fields? Edit: Found it index=True. I noticed, the online editor has no such options. Could be handy. Also the page about index=True and composite_index(a, b) should link each other: https://docs.ponyorm.com/api_reference.html?highlight=index#cmdoption-arg-index and https://docs.ponyorm.com/entities.html?highlight=index#composite-indexes

Good catch. Thanks!

The primary key is an index already in any db

Yeah, so it is and index.

Maybe we can allow index=True option for PrimaryKey attributes which will do nothing

Yep, that was my thinking...

Not sure if that makes sense. You guys gonna decide.

Ok, I'll think about it

I would say no. I also noticed that I have no problems on my local machine, and on server log I found these entries in correspondence of python errors (same time): SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request...

Pony ORM is able to reconnect automatically if the connection turns out to be closed at the start of transaction. This way it is not necessary to set any timeouts, because MySQL should close connection after the period of inactivity, that is, between transactions, and Pony is able to handle it. But it seems that in your case the connection is dropped in the middle of transaction for some reason. I think, this lead to error 2013 "Lost connection to MySQL server during query". There may be many different reasons for that error. For example, "you may be experiencing a problem with BLOB values that are larger than max_allowed_packet, which can cause this error with some clients. Sometime you may see an ER_NET_PACKET_TOO_LARGE error, and that confirms that you need to increase max_allowed_packet." https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html http://stackoverflow.com/questions/6516943/lost-connection-to-mysql-server-during-query I'll add the possibility to set a bigger timeout, but I'm doubting that the timeout is the real reason for the problem

:/ Now the database query still is taking 8 seconds, which is to long for inline bots Trying to optimze the query. I have two subclasses of Tags, which I want to match, but I get TypeError: Function 'isinstance' cannot be used this way: isinstance(t, StickerTag) I'll post code next.

Python: # get stickers query_text = "MLP" query_text_lower = query_text.lower() orm.sql_debug(True) pairs = orm.select( (st.sticker.file_id, max(st.date)) for st in StickerMessage if ( query_text == st.sticker.emoji or query_text == st.sticker.file_id or query_text_lower in st.sticker.sticker_pack.url.lower() or query_text_lower in st.sticker.sticker_pack.title.lower() or orm.exists(s_t for s_t in st.sticker.tags if query_text_lower in s_t.string.lower()) or orm.exists(p_t for p_t in st.sticker.sticker_pack.tags if query_text_lower in p_t.string.lower()) ) and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") and not orm.exists(t for t in st.sticker.sticker_pack.tags if t.string.lower() == "nsfw") ).order_by(orm.desc(2), 1).limit(50, offset=offset) orm.sql_debug(False) Resulting SQL: SELECT "sticker"."file_id", MAX("st"."date") FROM "stickermessage" "st", "sticker" "sticker", "stickerpack" "stickerpack" WHERE ('MLP' = "sticker"."emoji" OR 'MLP' = "sticker"."file_id" OR lower("sticker"."sticker_pack_url") LIKE ('%%' || replace(replace(replace('mlp', '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' OR lower("stickerpack"."title") LIKE ('%%' || replace(replace(replace('mlp', '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' OR EXISTS ( SELECT 1 FROM "tag" "s_t" WHERE "st"."sticker_file_id" = "s_t"."sticker_file_id" AND "st"."sticker_emoji" = "s_t"."sticker_emoji" AND "s_t"."classtype" IN ('StickerTag') AND lower("s_t"."string") LIKE ('%%' || replace(replace(replace('mlp', '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' ) OR EXISTS ( SELECT 1 FROM "sticker" "sticker-2", "tag" "p_t" WHERE "st"."sticker_file_id" = "sticker-2"."file_id" AND "st"."sticker_emoji" = "sticker-2"."emoji" AND lower("p_t"."string") LIKE ('%%' || replace(replace(replace('mlp', '!', '!!'), '%%', '!%%'), '_', '!_') || '%%') ESCAPE '!' AND ("sticker-2"."sticker_pack_url" = "p_t"."pack") )) AND NOT EXISTS ( SELECT 1 FROM "tag" "t-1" WHERE "st"."sticker_file_id" = "t-1"."sticker_file_id" AND "st"."sticker_emoji" = "t-1"."sticker_emoji" AND "t-1"."classtype" IN ('StickerTag') AND lower("t-1"."string") = 'nsfw' ) AND NOT EXISTS ( SELECT 1 FROM "sticker" "sticker-3", "tag" "t-2" WHERE "st"."sticker_file_id" = "sticker-3"."file_id" AND "st"."sticker_emoji" = "sticker-3"."emoji" AND lower("t-2"."string") = 'nsfw' AND ("sticker-3"."sticker_pack_url" = "t-2"."pack") ) AND "st"."sticker_file_id" = "sticker"."file_id" AND "st"."sticker_emoji" = "sticker"."emoji" AND "sticker"."sticker_pack_url" = "stickerpack"."url" GROUP BY "sticker"."file_id" ORDER BY 2 DESC, 1 LIMIT 50 So I thought, lets simplify and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") and and not orm.exists(t for t in st.sticker.sticker_pack.tags if t.string.lower() == "nsfw") to be and not orm.exists(t for t in Tag if t.string.lower() == "nsfw" and ( (isinstance(t, StickerTag) and t.sticker == st.sticker) or (isinstance(t, PackTag) and t.pack == st.sticker.sticker_pack) )) But it seems isinstance is not usable?

isinstance is not supported in queries yet, but you can ommit it. Inside a query you can use attributes of subclasses: and not orm.exists(t for t in Tag if t.string.lower() == "nsfw" and ( t.sticker == st.sticker or t.pack == st.sticker.sticker_pack ))

isinstance is not supported in queries yet, but you can ommit it. Inside a query you can use attributes of subclasses: and not orm.exists(t for t in Tag if t.string.lower() == "nsfw" and ( t.sticker == st.sticker or t.pack == st.sticker.sticker_pack ))

Oh, okey

It should speed up your query, but the real slowness is from the fact that you use in operator for strings. This operation is not indexable. You can try to use .startswith instead, it should be considerably faster

I see.

You alse need to keep t.string.lower() value pre-calculated in a separate column, or use index by expression. Pony does not know how to work with such indexes yet, but you can create it manually

Modifying and not orm.exists(t for t in st.sticker.tags if t.string.lower() == "nsfw") and not orm.exists(t for t in st.sticker.sticker_pack.tags if t.string.lower() == "nsfw") To and not orm.exists(t for t in Tag if t.string.lower() == "nsfw" and ( t.sticker == st.sticker or t.pack == st.sticker.sticker_pack )) seems to already fast enough to answer in time, but the precalculated index is a nice Idea

I mean, have an attribute normalized_string = Required(str, index=True)

and use it instead of t.string.lower()

with .startswith

Could I use CREATE UNIQUE INDEX lower_case_string ON Tag ((lower(string)));? As seen in http://stackoverflow.com/a/3980083/3423324

I think yes. But you need to create it manually, Pony does not know how to create such indexes yet

But getting data with that index will work after that?

yes

I'll try both. Lets see.

Does pony provide timing information I can print?

yes

wait a sec

https://docs.ponyorm.com/api_reference.html#QueryStat

But I'd need to know my SQL to get the timing?

yes

You can list all queries with their time

also, db.last_sql returns last SQL query

<pony.orm.core.QueryStat object at 0x7f30dd257860> Lol

Docs could mention that "time spent" is in seconds Anyway added that to the wiki: https://github.com/ponyorm/pony/wiki/Timing-of-last-query

<pony.orm.core.QueryStat object at 0x7f30dd257860> Lol

A string representation would be handy here I think.

Everything except the SQL

Everything except the SQL

Looks like a good idea

pony.orm.dbapiprovider.ProgrammingError: missing FROM-clause entry for table "sticker-2" LINE 1: ..."p"."title", "p"."url", COUNT(DISTINCT case when ("sticker-2... Line is this: packs_n_sum = orm.select((p.title, p.url, orm.count(p.stickers)) for p in StickerPack if not exclude_single or orm.count(p.stickers) > 1).order_by(orm.desc(3), 1).limit(limit)

can you show the SQL query?

ye, hold on

sticker_tag_test_1 | SELECT "p"."title", "p"."url", COUNT(DISTINCT case when ("sticker-2"."file_id", "sticker-2"."emoji") IS NULL then null else ("sticker-2"."file_id", "sticker-2"."emoji") end) sticker_tag_test_1 | FROM "stickerpack" "p" sticker_tag_test_1 | LEFT JOIN "sticker" "sticker" sticker_tag_test_1 | ON "p"."url" = "sticker"."sticker_pack_url" sticker_tag_test_1 | GROUP BY "p"."title", "p"."url" sticker_tag_test_1 | HAVING (%(p1)s OR COUNT(DISTINCT case when ("sticker"."file_id", "sticker"."emoji") IS NULL then null else ("sticker"."file_id", "sticker"."emoji") end) > 1) sticker_tag_test_1 | ORDER BY 3 DESC, 1 sticker_tag_test_1 | LIMIT 100

exclude_single is False

Try to replace orm.count(p.stickers) to count(st for st in p.stickers)

the one in the if, the one at the beginning or both?

I mean it should be a Set, but p.stickers.count() is not defined it says

The one at the top

The error is a bug, but befor I will be able to fix it you can reword a query a bit so it will work