So with yield from objects it does, and I can't do that. Thanks.

Hello! Why pony do lazy load for queries like: select((x, y) for x in X for y in Y if x.key1 == y.key2)

Is it possible preload all attrs?

So with yield from objects it does, and I can't do that. Thanks.

I mean, if you create initial query outside of db_session, and retrieve objects later inside db_session (as you do) it probably should work. But this is not tested

Hello! Why pony do lazy load for queries like: select((x, y) for x in X for y in Y if x.key1 == y.key2)

Pony do this because if you retrieve several objects at row like select((x, y) ... then there may be duplicates, and if query contains grouping it may interact badly with some column types like BLOB, etc. Because of this, Pony retrieve only primary keys, and load other attributes in a separate query. In many cases it should be more efficient than to retrieve objects with duplicates in a single query

Why do you want to preload all attrs?

I have 2 attr in X and 1 in Y except primary key. I use Mixins for convert attr from db to python objects.

I don't understand the part about mixins

What is IDE that work flawlessly with pony?

In most cases PyCharm works good

class X(Ent): x = Required(JSON) def xx(self): return convert(self.x)

Or class X(Ent, Mix):

In most cases PyCharm works good

Yeah

class Mix(): def xx...

Does pony automatically creates new Tables if I add a db.Entity, and use db.generate_mapping(create_tables=True), even if there already are Tables?

Is it possible preload all attrs?

Currently there is no way to load pairs of objects in a single query, but I don't think the additional query hurts performance much in this case, especially if the additional field is of JSON type

does the community edition works well either?

Does pony automatically creates new Tables if I add a db.Entity, and use db.generate_mapping(create_tables=True), even if there already are Tables?

Yes, Pony will create table automatically. But if your previous entity references newly added entity, it may be necessary to add column to a previous table

does the community edition works well either?

Yes

okay,I ll tried it.. I usually use spyder to write python code.

Thank you.

have tried pycharm but it's so heavy in my old pc..I think I have to buy a new one

Jetbrains give free licence for students

Do I have to register to give jetbrains a try?

For try not

I am opening the site right now..

I ll read it ..;.thanks all

Working with PyCharm, and boy, it is great!

I m sure it is..

TypeError: 'unique' option cannot be set for PrimaryKey attribute Shouldn't Primary Keys be unique?

It is already unique

I think that shoudn't crash the app, though.

Hello! Is it possible to use postgres int arrays? I try to implement similarity search

I found smlar extension for postgres.

This extension provide % operator

For queries like: select * for E where arr %'{1,2,3}'

I would need to use two different databases inside my PONY setup? Both are running on the same mysql server. What's the best way to do so? Currently I only use one database.

If those database are mostly independent, you can create two different files with model definitions, and use two different Database objects. Pony allows to work with two different databases in the same db_session. If you always use these two databases together, another options is to have a single file with model definitions with a single db object. In that case you need to specify table name for each entity and many-to-many relation whose database differ from the database specified in db.bind(...) options: class MyEntity1(db.Object): _table_ = ['database1', 'table1'] name = Required(str) class MyEntity2(db.Object): _table_ = ['database2', 'table2'] foo = Required(int) class MyEntity3(db.Object): # the table placed inside the default database bar = Required(int) In MySQL terms, database is a synonym to schema, and you can work with two different schemata placed on the same server thgrough a single connection

Hello! Is it possible to use postgres int arrays? I try to implement similarity search

Right now Pony does not support PostgreSQL arrays, we plan support it in the future. You can try to write raw_sql query fragment

Right now it is better to use JSON if possible

they are completely independent :)

Then just two different Database objects, and two different files with entity definitions

Are there some docs how to "create" these files because at the moment all my models are inside my "main" py file

Can I define some default values inside my models?

For example the attribute "created_at" -> current timestamp, this would save a lot of work when creating new instances inside dbase

Just make a separate file, for example models.py with database object and entity definitions: from pony import orm db = orm.Database() class MyEntity(db.Entity): name = orm.Required(str) And then in you main file you can do from models import db db.bind('mysql', ...) db.generate_mapping(create_tables=True) The same with another database

For example the attribute "created_at" -> current timestamp, this would save a lot of work when creating new instances inside dbase

import datetime class MyEntity(db.Entity): created_at = Required(datetime.datetime, default=datetime.datetime.now)

Right now it is better to use JSON if possible

Does using json has any drawbacks on the postgres side?

In general, I think no. Some PostgreSQL functions may work with native arrays only, like % operator that @nougmanoff want to use

@akozlovsky when using this code now I get: NameError: name 'PrimaryKey' is not defined

-> https://puu.sh/vtrrG/81a02b1654.png

You need to write orm.PrimaryKey

Or write from pony.orm import * and don't use orm. prefix

@akozlovsky after setting up all my models inside this external files I've only included "from models_server import db" inside my "main" file. Now how to access the class called: "GameServer" I've created inside the models_server?

You can access it as an attribute of db object: db.GameServer Or you can import it from models_server in a usual way

:) in case you need some servers for PonyORM just drop me a line, that good support...I would need to do something for my karma

Thanks, I will keep this in mind ;)

Is there any way to generate a timestamp of 0000-00-00 00:00:00 in python?

using datetime.datetime(0, 0, 0, 0, 0) doesn't work -> ValueError: year is out of range

All I found was: http://stackoverflow.com/questions/35851104/how-to-initialize-datetime-0000-00-00-000000-in-python

Probably there is no way, because this is not a valid date

Okay np :)

ok. Anybody know how to create transparent converter on postgres side from/to json <> int array?

According to this stackoverflow answer https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array/54289#54289 You can make PostgreSQL function

CREATE OR REPLACE FUNCTION jsonb_arr2text_arr(_js jsonb) RETURNS text[] AS $func$ SELECT ARRAY(SELECT jsonb_array_elements_text(_js)) $func$ LANGUAGE sql IMMUTABLE;

And then use it in your raw_sql queries with Pony:

select(x for x in MyObject if raw_sql(""" jsonb_arr2text_arr(x.json_field -> 'items') % {1, 2, 3} """)

Maybe I'm wrong in some details, but probably you will be able to make it working

Thank you! I will try it.

This function returns array of text elements, you need int elements instead, maybe you can change function to return array of ints

yes. i see

CREATE OR REPLACE FUNCTION jsonb_arr2int_arr(_js jsonb) RETURNS int2[] AS $func$ SELECT ARRAY(SELECT jsonb_array_elements_text(_js)::INT2) $func$ LANGUAGE sql IMMUTABLE;

this work

cool

from pony.orm import * db = Database() sql_debug(True) class A(db.Entity): _table_ = ('tst', 'a') a = Required(Json) db.bind('postgres', user='postgres', password='xxx', host='localhost', database=None) db.generate_mapping(create_tables=True) my test DB. select(x for x in A if raw_sql("json_int2(ta.a) % '{10,9,8,7,6,5,4,3,2,1}'::INT2[]")).limit(50) my query. GET NEW CONNECTION SWITCH TO AUTOCOMMIT MODE SELECT "x"."id", "x"."a" FROM "tst"."a" "x" WHERE json_int2(ta.a) % '{10,9,8,7,6,5,4,3,2,1}'::INT2[] LIMIT 50 debug sql. but I get error: ValueError: unsupported format character 0x20 at index 66

10 [122, 39, 85, 109, 112, 104, 7, 60, 192, 162, 64, 148, 79, 111, 165, 97, 176, 175, 135, 131, 26, 58, 36, 167, 22, 62, 74, 45, 130, 194, 129, 199, 120, 119, 14, 168, 190, 142, 68, 159, 4, 16, 169, 43, 50, 125, 134, 178, 132, 93, 34, 140, 10, 121, 173, 57, 28, 6, 96, 123, 44, 83, 133, 70, 127, 66, 54, 77, 114, 161, 105, 124, 78, 67, 158, 113, 146, 116, 32, 185, 128, 117, 11, 196, 160, 153, 188, 1, 195, 118, 81, 92, 95, 75, 94, 30, 73, 3, 29, 18] example of row in db

in postgres console works fine

Try to write %% instead of % inside your raw_sql string

works!

Thank you very much!

Sure

Best pytype for storing images?

Actually

Wouldl it be better to store images in db or filesystem

in file system

kk

great

Wouldl it be better to store images in db or filesystem

Yeah, also you can backup them better. Think of the huge export.sql file.

How would I unload a database? I am trying to realize the following steps, basically changing out the database schema, after I ran migrations, which should lead to the new schema file Example: from model.v0 import db db.bind("postgres", host=POSTGRES_HOST, user=POSTGRES_USER, password=POSTGRES_PASSWORD, database=POSTGRES_DB) db.generate_mapping(create_tables=True) from migrations.v0 import do_migrate do_migrate(db) del db from model.v1 import db # repeat the steps with v1

So the question is, do I need to remove/unload the old db somehow?

I thought you had plan to copy data from one database to another. If it is still true, than you need to create a new database, write a script which copies data from one database to another, and then, after you check that new database is correct, you can just drop previous database manually

Currently I want to write a small migrations system myself, so I don't have to do migtations in phppgadmin anymore.

So the idea is to have this file which will do the migrations, and a file with the database definitions (Entity s) before, to be able to use pony orm syntax (not only raw SQL) in them. After it modified the table, I'd need to replace the db, loading a file with newer definitions.

Ok, I think it is not necessary to unload the previous db object, if you run migration as a script which run separate from your main application

It would run as the main app, on startup, and the migration module should return the latest database object. But you are right, in that case I have to make sure I don't start multible instances at the same time.

It is better to have special migration script which run during deploy

I don't really have a deploy phase, as I am running it in docker containers. So everything is done "on startup".

I made a migration script. Can be found at https://github.com/luckydonald/pony_up