Lucky
Lucky
Lucky
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
Romet
i think that's a limit of nesting your comprehensions like that
Romet
since you only have a single if case for the bulk of it, rather than have them separately based on your comprehensions
Lucky
Yes, I think so, too.
But how would I seperate them?
Romet
are you sure that syntax is correct?
Lucky
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
Lucky
Alexander
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(...)
Lucky
I'll try 1st. Thanks
Lucky
Let's see If I can get it running
Lucky
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
Alexander
Offtopic: are you sure you want to find tags which string contains query_text, but not equal to it?
Lucky
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
Lucky
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.
Claudio
Hello everyone
Claudio
How do I add a new column to the table by ORM without deleting the table before?
Claudio
deleting = dropping
Alexander
Hi @Chackero, we are making migration tool for that, it is almost done
stsouko
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.
Alexander
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)
stsouko
Thank you!
Claudio
Hi @Chackero, we are making migration tool for that, it is almost done
OK. I'll wait. Thank you.
Anonymous
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.
Alexander
Hi, Pony ORM should be able to handle this error automatically. Can you provide a more detailed traceback?
Anonymous
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'))
Alexander
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.
Anonymous
Thanks! FYI I checked and the traceback for OperationalError: 2013 is exactly the same as the one for 2006.
Alexander
Do you have some long-running transactions?
Anonymous
Are there any contribution guidelines for the project?
Alexey
Are there any contribution guidelines for the project?
What kind of functionality you'd like to contribute?
Anonymous
I have a student (< 6months programming experience) who wanted to get started contributing to OSS, and he made a PR to add some docstrings
Anonymous
I've never contributed to OSS and want to be as helpful as I can be
Anonymous
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...
Lucky
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
Lucky
Having url = PrimaryKey(str, index=True)
Raises
pony.orm.core.DBSchemaError: Index for column 'url' already exists
Is that intentional?
Alexey
The primary key is an index already in any db
Lucky
Yeah, so it is and index.
Alexander
Maybe we can allow index=True option for PrimaryKey attributes which will do nothing
Lucky
Yep, that was my thinking...
Lucky
Not sure if that makes sense. You guys gonna decide.
Alexander
Ok, I'll think about it
Alexander
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
Lucky
:/ 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.
Lucky
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?
Alexander
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
))
Lucky
Alexander
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
Lucky
I see.
Alexander
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
Lucky
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
Alexander
I mean, have an attribute
normalized_string = Required(str, index=True)
Alexander
and use it instead of t.string.lower()
Alexander
with .startswith
Lucky
Could I use CREATE UNIQUE INDEX lower_case_string ON Tag ((lower(string)));?
As seen in http://stackoverflow.com/a/3980083/3423324
Alexander
I think yes. But you need to create it manually, Pony does not know how to create such indexes yet
Lucky
But getting data with that index will work after that?
Alexander
yes
Lucky
I'll try both. Lets see.
Lucky
Does pony provide timing information I can print?
Alexander
yes
Alexander
wait a sec
Alexander
https://docs.ponyorm.com/api_reference.html#QueryStat
Lucky
But I'd need to know my SQL to get the timing?
Alexander
yes
Alexander
You can list all queries with their time
Alexander
also, db.last_sql returns last SQL query
Lucky
<pony.orm.core.QueryStat object at 0x7f30dd257860> Lol
Lucky
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
Lucky
<pony.orm.core.QueryStat object at 0x7f30dd257860> Lol
A string representation would be handy here I think.
Lucky
Everything except the SQL
Lucky
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)
Alexander
can you show the SQL query?
Lucky
ye, hold on