Alexander
Also SQL debug flag is thread-local now
Matthew
sql_debugging looks cool :)
Matthew
Does it include query execution times?
Alexander
You mean 'does it show how much time query was executing?'?
Alexander
At this moment to see statistics you need to look at db.local_stats. But in the future we can add some summary statistics output to sql_debugging
Matthew
Alexander 1: Yep
Alexander 2: Cool :)
Micaiah
2nd on thank you for Pony, doubley so when migrations come out ;)
Matthew
I just found this while searching for pony stuff:
Matthew
https://pypi.python.org/pypi/flask-ponywhoosh
Alexander
That's interesting
Alexander
riccardo
Hello people,
first of all thanks a lot for the great work. I'm really enjoying using ponyorm
Dumb question:
what's the correct way to retrieve the value of a column from a query result, if the column name is inserted by an user?
Eg. I have this code:
column_name = "some_column_name"
user_id = 1234
u = User[user_id]
how can I get/change the column_name value of u? I tried with a wild print(u[column_name]), but it's not the way
I tried to take a look at the docs/examples and google something, but I haven't been able to find an inspiration :(
Alexander
I think that u in your case is entity, so it has no column. It has several attributes which of them have one or more columns.
Alexander
User[1234] is the line of code which means like: "Give me object from entity User with primary key 1234".
Alexander
You can use standard Python getattr function:
u = User[user_id]
value = getattr(u, column_name)
print(value)
riccardo
I think that u in your case is entity, so it has no column. It has several attributes which of them have one or more columns.
Shouldn't it be just one entity corresponding to the user with ID 1234? I mean, I am able to access the column content from u.some_column_name 🤔
riccardo
You can use standard Python getattr function:
u = User[user_id]
value = getattr(u, column_name)
print(value)
I thought about this after closing up everything eh, thanks. Will try today
Alexander
According to Pony terminology:
User is an entity class. Corresponds with a table in the database.
u = User[123] is an entity instance (also may be called as entity object). Corresponds with a row in a table.
User.name is an entity attribute. Corresponds with a column in a table.
Some attributes are virtual, and don't have corresponding column. Most typical examples are collections, like User.posts
Anonymous
Does Pony is ready for Production / Deployments?
Matthew
In my experience, yes
Alexander
It depends on, whether you need migrations to modify database schema. Official migration tool is not ready at this moment, we are working on it and hope it will be released soon. Until this moment, you can do migrations manually, typically it is easy to write ALTER TABLE command if you know the syntax. Another option is to use migration tool written by @luckydonald: https://github.com/luckydonald/pony_up
Anonymous
so anything is ready enough except only it's migration tool ?
Alexander
I think yes. There are things that we plan to add in the future, like, upsert operation, abstract base entity classes, support of SELECT ... FROM (SELECT ...) queries, better to_json API, but even without these things PonyORM is ready enough
Anonymous
Okay,, I just make the GUI for my applications., I ll try to make the database using Pony
Anonymous
wish me luck
Anonymous
I don't have to use pony to migrate database right?
Alexander
Last years we used pony to develop fineartbiblio.com. It is a site with a pretty complex database schema. On this site we write migrations manually. Typically it is pretty easy to formulate necessary ALTER TABLE command.
The benefits of migration tool is that it can be executed as a part of automatic deployment process. Also, it can generate simple migrations automatically which may be convenient for people who doesn't know the syntax
stsouko
hello!
for query:
E.select(lambda x: not x.a)
I get:
SELECT "x"."id", "x"."a"
FROM "schema"."a" "x"
WHERE NOT (length("x"."a") > 0)
db:
class A(db.Entity):
_table_ = '%s_a' % schema if DEBUG else (schema, 'a')
id = PrimaryKey(int, auto=True)
a = Optional(str)
stsouko
why not
WHERE (length("x"."a") == 0)
or another
stsouko
I use postgres
riccardo
According to Pony terminology:
User is an entity class. Corresponds with a table in the database.
u = User[123] is an entity instance (also may be called as entity object). Corresponds with a row in a table.
User.name is an entity attribute. Corresponds with a column in a table.
Some attributes are virtual, and don't have corresponding column. Most typical examples are collections, like User.posts
Yes, that's clear in my mind - I just have to try that getattr() thing and see if it works
Matthew
hello!
for query:
E.select(lambda x: not x.a)
I get:
SELECT "x"."id", "x"."a"
FROM "schema"."a" "x"
WHERE NOT (length("x"."a") > 0)
db:
class A(db.Entity):
_table_ = '%s_a' % schema if DEBUG else (schema, 'a')
id = PrimaryKey(int, auto=True)
a = Optional(str)
E.select(lambda e: e.a is None) might give you better SQL?
stsouko
Yes. Thank you.
Alexander
Can you post this sql? I'm wondering.
stsouko
E.select(lambda x: x.a is None) => WHERE "x"."a" IS NULL
Alexander
Yes, does it work as you expect?
Cause PostgreSQL does not threat empty string as NULL, as I remember.
Matthew
The default for Optional(str) is NULL / None
Matthew
so it's not an empty string
Matthew
I sometimes do Optional(unicode, default='') where it makes sense
Alexander
I just clarified that moment. Because I saw comparing length with zero.
Alexander
That if he needs empty strings that way will work in Oracle, not Postrgre.
Valentin
Is there a way to fetch specific field after filtering?
For example:
select(application for application in orm.Applications).filter(labda a.year == 2017).get(lambda a: a.id)
So I get filtered applications' ids.
Matthew
An inefficient option is [x.id for x in select(application for application in orm.Applications).filter(labda a.year == 2017).get(lambda a: a.id)]
Valentin
Yeah, I already did it but as it builds SQL query it should be possible to fetch records without fetching whole object (lazy isn't good solution too).
Alexander
If you know in advance than you need that specific attribute, you can write
query = select(a.id for a in orm.Applications)
query = query.filter(lambda: a.year == 2017)
Note that in that case I don't specify lambda argument in filter, because an argument will refer to a.id expression, and we need access original object instead
If the attribute name is not known in advance, you can use getattr function:
attr_name = <get attr name>
query = select(getattr(a, attr_name) for a in orm.Applications)
query = query.filter(lambda: a.year == 2017)
There is an open issue with getattr: https://github.com/ponyorm/pony/issues/223
I'll try to fix it
Valentin
Wow, that's not really intuitive. I can't even imagine what will happen on double for query.
Valentin
But on other hand it just use name that I've defined in select.
Valentin
I wonder if linter will cry about it.
Valentin
yeah, it will
Valentin
I think it would be better to have something like .map() for query result that will take a lambda that will process something that will be result of generator in select (just like in filter)
Valentin
As an option atleast
Alexander
It will. I have a plan to introduce another method where that will use original names from the query, but have it explicitly specified as lambda arguments.
Right now in expression:
select(a.b for a in X).filter(lambda y: y > z)
y refers to query result a.b, the name of y argument may be any name
With new where method:
select(a.b for a in X).where(lambda a: a.c > z)
a will refer to loop variable with the same name
Alexander
I think it is a good idea to implement map method. I think we should add it
Anonymous
Hello all -
I've hit a snag using Python Pandas where I have some NaN values which I have to manually cast to None before storing to a DB using Pony.
I was wondering if there was any thoughts around NaN values being understood as Null in Pony? Any reason this is a bad idea?
Matthew
Hello all -
I've hit a snag using Python Pandas where I have some NaN values which I have to manually cast to None before storing to a DB using Pony.
I was wondering if there was any thoughts around NaN values being understood as Null in Pony? Any reason this is a bad idea?
Where I want to cast a string to None (not with Pandas but in similar situations), I do "v or None", where v is the variable, so '' would be cast to None, maybe you can do that with NaN?
Anonymous
Unfortunately it's not so simple. Though it's not terribly complicated either. If my dataframe (think if it as an excel table with labelled columns) has some column 'Name' I need to type:
dataframe['Name'] = dataframe['Name'].where((pd.notnull(dataframe['Name'])), None)
Yuck! That is just a result of the design of Pandas however. It is an otherwise beautiful library and I don't mean to cast a shadow on it. I just wondered if there were some way to specify what maps to NULL in Pony, or if NaN was considered a member of that list.
Alexander
I think it is possible to store NaN as a valid float value, at least in some database. So it may be logically not correct to always convert it to None.
I think, when you assign value to pony, you almost certainly have some code like
pony_object = MyEntity(
attr1=<some complex expr>,
attr2=<another complex expr>)
or
pony_object.attr = <some complex expr>
You can write a simple function NaNtoNone and then use it in a following way:
pony_object = MyEntity(
attr1=NaNtoNone(<some complex expr>),
attr2=NaNtoNone(<another complex expr>))
or
pony_object.attr = NaNtoNone(<some complex expr>)
Anonymous
I think it is possible to store NaN as a valid float value, at least in some database. So it may be logically not correct to always convert it to None.
I think, when you assign value to pony, you almost certainly have some code like
pony_object = MyEntity(
attr1=<some complex expr>,
attr2=<another complex expr>)
or
pony_object.attr = <some complex expr>
You can write a simple function NaNtoNone and then use it in a following way:
pony_object = MyEntity(
attr1=NaNtoNone(<some complex expr>),
attr2=NaNtoNone(<another complex expr>))
or
pony_object.attr = NaNtoNone(<some complex expr>)
I looked it up and you're right at least for Postgres (https://www.postgresql.org/docs/9.3/static/datatype-numeric.html)
That's a nice work around you suggested too. Personally I am going to go with a similar approach:
python
pony_object = MyEntity(**panda2pony(my_dictlike_pandas_object))
which will hide the other gory details I've not mentioned.
By the way how do you colour your text in Telegram? Sorry to be off-topic.
Alexander
I use single backquotes around a single word or triple-backquotes before and after a code block
Alexander
But Telegram doesn't do color-hinglighting yet, as far as I know
Anonymous
Oh, I'm looking at my mobile and not seeing colour but on my browser on PC I saw your code in blue, but my own markdown had no colour. Ubuntu+Firefox. Interesting to note. Thanks.
Lucky
Oh, I'm looking at my mobile and not seeing colour but on my browser on PC I saw your code in blue, but my own markdown had no colour. Ubuntu+Firefox. Interesting to note. Thanks.
On mac os the code is ```red``` and `green` depending on which code ticks you used.
Matthew
Is there an easy way in pony to say "only select records with an even id", aka modulus of 2?
Matthew
Use case is splitting some cpu intensive work between a few processes
Matthew
Doesn't need to be a perfectly even split
Alexander
select(x for x in MyEntity if raw_sql("x.id % 2") == 0)
Matthew
Is that supported in all pony supported databases?
Alexander
It should work in SQLite, PostgreSQL and MySQL. For Oracle it is necessary to write raw_sql("MOD(x.id, 2)") instead.
Matthew
Cool, thanks, I will use it then. One day it would be nice to have pony translate % into sql
Valentin
Maybe it's not a bad idea to implement some db specific functions for databases that have this function and raise exception if current driver doesn't support it.
Alexander
Yes, I think it is possible. Regarding % operations, I agree that we need to add support of it in all databases
Matthew
select(u for u in User if raw_sql("u.id % 4") == 0).random(5) gives TypeError: 'dict' object does not support indexing
Matthew
Is this a recently fixed bug in master?
Alexander
It seems that the bug is not in Pony. I tested on 0.7.2 and master, and the following code works without any problem:
>>> from pony.orm.examples.university1 import *
>>> select(s for s in Student if raw_sql('s.id % 4') == 0).random(5)
Matthew
that example also works for me, that is weird
Alexander
Maybe you can show traceback of your error
Alexander
I was able to reproduce. The reason for the error is, PostgreSQL psycopg2 driver uses % symbols to represent query parameters, so in PostgreSQL it is necessary to double % sign inside RawSQL expression:
select(u for u in User if raw_sql("u.id %% 4") == 0).random(5)
It seems that without native Pony support of modulo division its using is a bit cumbersome. Probably we need to add it sooner
Matthew
Thank you very much!