Hey, again asking how the migration is progressing :D But probalby have a problem it won't be able to tackle in the first version anyway. I need to change a primary key to normal (indexed) column. More specific, in my database I was storing sticker per file_id, but telegram recently changed how they are calculated. I am able to calculate the new file_ids from the old ones, but I can't update them, because the file_ids are primary key. So I think adding a normal counter based PK would be the easiest way? But it will be fun to update all the references.

(actually there is a real id on telegram side, but I don't always have them, when puting stickers in the database)

This is pseudo optimal, because some stickers ended up already twice in the database this way.

Hi, I just started to use ponyorm in a little project ?

hello :) good choise

But most plugins for flask support peewee or sqlalchemy =\

But most plugins for flask support peewee or sqlalchemy =\

yea :/

PonyORM needs better plugin support

Although I don't neven know what Flask Pony would do, I've never felt like I needed it at least

Although I don't neven know what Flask Pony would do, I've never felt like I needed it at least

Me neither. And I use flask for everything :D

Hey, again asking how the migration is progressing :D But probalby have a problem it won't be able to tackle in the first version anyway. I need to change a primary key to normal (indexed) column. More specific, in my database I was storing sticker per file_id, but telegram recently changed how they are calculated. I am able to calculate the new file_ids from the old ones, but I can't update them, because the file_ids are primary key. So I think adding a normal counter based PK would be the easiest way? But it will be fun to update all the references.

Oh boy, this is driving me mad

@luckydonald the first version of migration tool will not be able to change primary keys. I think you can use the following approach: create a separate database with fixed Sticker definition and write a script which connects to both databases and copies data from one database to another one

I have an idea for explicit set composit_key/index per column ordering: class A(Entity): field_one = orm.Required(int) field_two = orm.Optional(str) orm.composit_index(field_one, field_two) # ... ("field_one", "field_two) orm.composit_index(field_one, core.desc(field_two)) # ... ("field_one", "field_two" DESC) ...

It will be usefull use api like this

So you suggest to allow using desc when specifying composite indexes. I think it is possible

Yea!

So you suggest to allow using desc when specifying composite indexes. I think it is possible

I just know about postgres, but there you can also index stuff like LOWER(column)

Well, actually it can be arbitrary expressions... In the future we should support it as well, but before that I want to finally release migrations

Is there already documentation or something else to read about that will work for the user?

Not yet. In short, we will replace db.bind(**settings.db_params) db.generate_mapping(create_tables=True) to db.connect(**settings.db_params) and allow a developer to define the following script migrate.py in his project folder: import settings from models import db db.migrate(**settings.db_params) Then a developer can run python migrate.py make to generate new migration file, python migrate.py apply to actually perform migration or python migrate.py sql to show sql of the migration It is also possible to call python migrate.py make --custom to create a file of migration in which it is possible add arbitrary UPDATE or ALTER TABLE command manually. The details of API will be revealed soon

Not yet. In short, we will replace db.bind(**settings.db_params) db.generate_mapping(create_tables=True) to db.connect(**settings.db_params) and allow a developer to define the following script migrate.py in his project folder: import settings from models import db db.migrate(**settings.db_params) Then a developer can run python migrate.py make to generate new migration file, python migrate.py apply to actually perform migration or python migrate.py sql to show sql of the migration It is also possible to call python migrate.py make --custom to create a file of migration in which it is possible add arbitrary UPDATE or ALTER TABLE command manually. The details of API will be revealed soon

How will versioning of the API work? Add `db = orm.Database(version=3)` In one file, and `db = orm.Database(version=4)` In the next? So it can detect the current state and upgrade accordingly?

The plan is the following: 1) When you upgrade to Pony 0.8, you need to change db.bind(**settings.db_params) db.generate_mapping(create_tables=True) to db.connect(**settings.db_params) 2) Pony will create a new table pony_version which has a single column pony_version with a single value. Looking at the value in this table, Pony can tell which upgrades to database structure should be applied in order to work with this database using the current version of Pony. If pony_version table is missing, but there is at least one other table for some entity, Pony consider database schema version as a "pre-0.8". 3) It is an error if you try to connect to a database using a new version of Pony if the database contains obsolete structure of tables. If you can perform migrate.py upgrade then the Pony perform necessary changes in a table structure to make them compatible with new version of Pony. As another option, you can specify allow_auto_upgrade=True option inside db_params when making db.connect(**db_params) call. In that case if you update Pony to a new version then Pony will perform the necessary upgrade automatically during the application start. 4) The changes that a necessary to upgrade existing database schema to a new Pony version is orthogonal to changes that you made in migrations. Migrations reflects logical changes in application data structure, while Pony upgrades does not change the logic of application. Pony perform schema upgrade when you update Pony to a new version. You make new migration when you change the logical model of application data.

So these migrationes are currently only ponyorm's own migrations, and not user defined changes? If so, Will there be an easy way to use it for own migrations? I heard django has a way of doing migrations if the user changed the data objects later. (Files numbered 01, 02, 03 etc., as far as I heard)

They are two different things, which will be introduced at the same time - Pony version-dependent upgrades and user-defined migrations. In order to start using user-defined migrations we need to rename many-to-many tables, that will be the first version-dependent upgrade which will be required to anybody who want to use previously defined tables with Pony 0.8

User-defined migrations will look pretty much like in Django

Hey folks, I'm running into a strange problem I can't seem to find elsewhere and just want to make sure I'm not "doing it wrong". I'm trying to use PonyORM to interface with a SQL database using a click.py cli in a single module—I declare the model as a global then start declaring click commands. In one of the commands I loop through a list that's created from a glob.glob, this all works fine, it accesses the model and creates the correct objects. The problem is when the @db_session starts to wrap up ( commit() ), ponyORM raises an pony.orm.core.UnexpectedError that the Object Map[UUID...] cannot be stored in the database. OperationError: no such table: Map. However, it does exist, and if I attempt to do it without looping through the list (inside the loop I create the objects) it works fine, same as if I do it from the shell. I've tried both @db_session decorators and with db_session: and also commit() within the loop. Any ideas? Here's the code: https://gist.github.com/xemoka/fe71d2a90795abfd2e282a535554c875

Try to add sql_debug(True) before db.generate_mapping(). You will be able to see which tables are created and how INSERT command looks

Try to add sql_debug(True) before db.generate_mapping(). You will be able to see which tables are created and how INSERT command looks

Ok, so I've updated the GIST, it doesn't seem to provide an answer, it grabs a new connection, begins an immediate transaction, Inserts and then rollsback: https://gist.github.com/xemoka/fe71d2a90795abfd2e282a535554c875#file-output-sh

It's that it's creating a new connection, but not to the same db?

It almost feels like a scope problem, but I don't quite understand how it loses it.

I don't understand the reason of the problem yet. Can you for test purpose specify the database filename in db.bind(...) with absolute path?

Wow. that's it.

Thank-you for your help!

It is strange. Probably click magic somewhat conflicted with pony magic. I'll investigate that

Yeah, no doubt... sometimes magic is great, sometimes it does this. Most of the time it's great ;) Pony is such a neat tool.

@RuralOtter can you please print db.provider.pool.filename after db.bind(...), after db.generate_mapping(...) and inside the @db_session? Is it different or the same?

without the absolute path, each time it outputs: .\mapcatalog.sqlite

What operation system do you use?

I've now put os.path.abspath("mapcatalog.sqlite") in the db.bind call.

this is on windows 7

Ok, thank you

My pleasure, glad to contribute—even if it's only bugs haha

Welcome!

Hello! is it possible to do st like this?: def load_tables(db, schema): class B(db.Entity): _table_ = (schema, 'molecule') a = Required('A') return B class A(db.Entity): b = Optional('B') b1=load_tables(db, 's1') b2=load_tables(db, 's2')

i want to create separate tables for B

You cannot have several entities with the same name in the same schema, but you can write generic code which creates similar entities with different names: from pony.orm.core import EntityMeta def load_tables(db, schema, class_name): attrs = {'a_id': Required(int)} cls = EntityMeta(class_name, (db.Entity,), attrs) return cls b1 = load_tables(db, 's1', 'B1') b2 = load_tables(db, 's2', 'B2') In your example class A has a relationship to class B. It should be relationship to a single class, so you cannot define it this way. You probably neet to use explicit id value instead and make joins manually

Thank you very much!

I spent an hour reading the source. but I did not understand how EntityMeta work

q2. is it possible to declare Entity in common way as class in load_tables? I use mixins in Entities.

I think it is hard to declare it as a class, because class cannot take class name from the variable, and we cannot use the same name for different entities. But if referential integrity is not important, you can define multiple databases, one database for each schema, and have the same entity in each of these databases. This way you can define entity as a class: def define_entities(db): class B(db.Entity): a_id = Required(int) db = Database() class A(db.Entity): pass db1 = Database() define_entities(db1) db2 = Database() define_entities(db2)

You can then use multiple databases in the same db_session. You cannot mix objects from the different databases inside the same select, but you can explicitly specify id value if you know it

Thank you!

Hi All - I'm new to PonyORM, and an indermediate pythoner (mostly jupyter notebook work - which will explain the basicness of this question), but I wanted to set up my entities and DB connections in a separate file that I can just reference from other scripts. This is eluding me right now as I tried to do all the db creation and binding in the init method of another class. The issue is obviously that then the entity classes can't access it. Any suggestions?

Can you show us your DB code? Either pasted here or in something like pastebn

just starting out with something for the NCAA tournament, but this is what I have right now with the obviously failing init in place -

from pony.orm import * class DataStruct(object): def __init__(self): db = Database() db.bind('sqlite', 'BracketDB.sqlite', create_db = True) db.generate_mapping(create_tables = True) sql_debug(True) class Team(db.Entity): name = Required(str) espn_id = PrimaryKey(int) players = Set('Player') games = Set('Game') class Game(db.Entity): home = Required(Team) away = Required(Team) date = Required(datetime) home_score = Required(int) away_score = Required(int) stats = Set('GameStats') class Player(db.Entity): espn_id = PrimaryKey(int) name = Required(str) year = Required(int)

In this code db.Entity only exists within the scope of DataStruct's init method

Take the __init__ code and move it to the module scope and it should work

from pony.orm import * db = Database() sql_debug(True) class Team(db.Entity): name = Required(str) espn_id = PrimaryKey(int) players = Set('Player') games = Set('Game') class Game(db.Entity): home = Required(Team) away = Required(Team) date = Required(datetime) home_score = Required(int) away_score = Required(int) stats = Set('GameStats') class Player(db.Entity): espn_id = PrimaryKey(int) name = Required(str) year = Required(int) db.bind('sqlite', 'BracketDB.sqlite', create_db = True) db.generate_mapping(create_tables = True)

actually you want to bind and generate after the Entities have been defined

got it, thanks - I'm not great at organizing code. Appreciate the help, really enjoying the way this package works so far

its a great package

the name attribute is unique, but the except clause isn't catching the error

it isn't making duplicates, but it is replying "Made: whatever" succesfully even though it isn't

Uh, what's that bot? (also whats `await`?)

(await is python 3.5's asyncio syntax)

The bot is for playing DnD on discord

kinda

oh cool

Why doesn't the try except work

never got any good introduction to await. how did you start?

reading David Beazley's post

and then this bot

its using discord.py, i think its a good itnro

intro*

since you are already comfortable with Pony and gen Python

you mean introduction to asyncio

should probably be searching for that rather than await

yeah

ah, thanks. will have a look tomorrow

I think async/await is better than the @coroutine way

anyway it's a state machine faking being threading

I think async/await is better than the @coroutine way

yeah, this I did some time ago

Micaiah - does your try except work without using async? Is it an issue with that or either way?

it needs async for the framework to work, and the rest of my orm calls work

Hm.. That's odd. Sorry, no idea. Any reason you're using with over the decorator

Yeah I couldn't get the decorator working with the @ls.command() decorator