Do you know a way to prevent that from happening?

btw i'm using sqlite

I get following problem

pony.orm.sqltranslation.IncomparableTypesError: Incomparable types 'datetime' and 'str' in expression: m.starting_at >= Match.get(id=3213173).starting_at

Seems like I have to add a detect_types=sqlite3.PARSE_DECLTYPES in the connect (didn't find how to do it with pony orm though)

https://stackoverflow.com/questions/4429788/reading-back-a-datetime-in-sqlite3/4430061

can someone help me? :D

Shouldn't the helper table of this actually index both colums and not only network_2?

In my entity I set a datetime attribute, when I get it from the db it is a str though

Are you sure you set up the model correctly? My first guess would be that you accidentially have a str as type there?

i did, it seems to be a common issue with sqlite

i'm figuring out how to configure it with pony orm

hope i dont need to use raw sql :D

Huh I guess you have to wait until some of those Alexs comes online

hope i dont need to use raw sql :D

Yeah, I'll just do this for now

seems like that doesn't work either for me, can't manage to bind the variables

pony.orm.dbapiprovider.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

In my entity I set a datetime attribute, when I get it from the db it is a str though

Hi Edis! Can you show your model definitions? SQLite does not have a binary format for datetime values, so Pony saves it as a strings and convert to datetime when read the values back from the database. You should not use sqlite3.PARSE_DECLTYPES, Pony perform the necessary conversions automatically

Shouldn't the helper table of this actually index both colums and not only network_2?

It is a bug indeed, it will be fixed when migrations come out (already fixed in migrations branch)

How is the status of that branch?

Is there a ticket tracking what's still left to do?

And how does one get involved?

It is a work in progress. We have a tests with different migrations, some of them still fail. I hope I'll have enough time during the rest of August to make it working on PostgreSQL.

How can I migrate from a SQLite database to Postgres?

You can create two db objects with same models but different connection parameters, select objects from one database and then create similar objects in another database. You probably need to manually split the process into sequential batches and process each batch in a separate db_session

Aww, I was kinda hoping there would be a migration tool, figuring that one out from the given data schema, like order of the tables and such.

from enum import Enum from datetime import datetime from pony.orm import * from persistence.models import db class Match(db.Entity): id = PrimaryKey(int) opponent = Required(Team) venue = Required(str) our_score = Optional(int) opponent_score = Optional(int) starting_at = Optional(datetime) is_full_time = Required(bool, default=False, sql_default=False) on_penalties = Required(bool, default=False, sql_default=False) season_start = Required(int) competition = Required(str) matchday = Required(str) goals = Set("Goal")

I'm trying to get a database lock for migrating with psycopg2. But for some reason I can't get past con.cursor()…

Anyone already had that issue?

It just seem to deadlock

Uh. seems to be an issue with the logger actually not logging the last message.

Hi

Is there any way to generate tables if they doesn't exists on start?

You can create tables using db.execute(...) or taking DBAPI connection using db.get_connection() If you want to make new tables available to Pony, you can create new db object with extended model set and bind it to the same database

So I need to generate them first?

I meant to auto generate them on start

What is your use-case?

I'd like to generate a sqlite db with tables if it doesn't exists on start

I already have the entities defined

db.generate_mapping(create_tables=True)

Oh

I was doing

db.bind(provider='sqlite', filename='db.sqlite', create_db=True) db.create_tables() db.generate_mapping()

Worked

Thanks very much

Sure

Sorry for being this dumb, I searched wrong on api

np

Hi Edis! Can you show your model definitions? SQLite does not have a binary format for datetime values, so Pony saves it as a strings and convert to datetime when read the values back from the database. You should not use sqlite3.PARSE_DECLTYPES, Pony perform the necessary conversions automatically

Hey, you can find them above. I've used raw sql for now to make the comparison for now.

``` from enum import Enum from datetime import datetime from pony.orm import * from persistence.models import db class Match(db.Entity): id = PrimaryKey(int) opponent = Required(Team) venue = Required(str) our_score = Optional(int) opponent_score = Optional(int) starting_at = Optional(datetime) is_full_time = Required(bool, default=False, sql_default=False) on_penalties = Required(bool, default=False, sql_default=False) season_start = Required(int) competition = Required(str) matchday = Required(str) goals = Set("Goal") ```

Hey, you can find them above. I've used raw sql for now to make the comparison for now.

It is possible that database row for Match[3213173] contains starting_at value in incorrect format. This is possible if you insert row directly without using Pony. In that case, when you do Match.get(id=3213173).starting_at, Pony reads row with incorrect starting_at value from the database, cannot convert it to date and leave it as is. Try to print it to see actual value

guys your SSL cert for ponyorm.org expired

Hi! Thanks, we'll fix it

Hi. Do you already have any expectations when migrations will appear in Pony?

I have to admit that with migrations it is difficult for me to make realistic estimates. I hope that by the end of August it will be possible to use migrations for PostgreSQL

yippee

I have to admit that with migrations it is difficult for me to make realistic estimates. I hope that by the end of August it will be possible to use migrations for PostgreSQL

will it be as easy as in Django?

It will be similar, but some functionality may be missing at start

It will be similar, but some functionality may be missing at start

Well, that's normal :) Very cool, waiting.

It is possible that database row for Match[3213173] contains starting_at value in incorrect format. This is possible if you insert row directly without using Pony. In that case, when you do Match.get(id=3213173).starting_at, Pony reads row with incorrect starting_at value from the database, cannot convert it to date and leave it as is. Try to print it to see actual value

I've only used pony to create everything (schema and entities). This is the format I get when I print starting_at: '2019-08-11 13:00:00+00:00' isinstance(str, match.starting_at) returns True Schema in sqlite looks like this sqlite> .schema Match CREATE TABLE IF NOT EXISTS "Match" ( "id" INTEGER NOT NULL PRIMARY KEY, "opponent" INTEGER NOT NULL REFERENCES "Team" ("id") ON DELETE CASCADE, "venue" TEXT NOT NULL, "our_score" INTEGER, "opponent_score" INTEGER, "starting_at" DATETIME, "is_full_time" BOOLEAN NOT NULL, "on_penalties" BOOLEAN NOT NULL, "season_start" INTEGER NOT NULL, "competition" TEXT NOT NULL, "matchday" TEXT NOT NULL ); CREATE INDEX "idx_match__opponent" ON "Match" ("opponent"); Model like this class Match(db.Entity): id = PrimaryKey(int) opponent = Required(Team) venue = Required(str) our_score = Optional(int) opponent_score = Optional(int) starting_at = Optional(datetime) is_full_time = Required(bool, default=False, sql_default=False) on_penalties = Required(bool, default=False, sql_default=False) season_start = Required(int) competition = Required(str) matchday = Required(str) goals = Set("Goal")

Pony doesn't store timezone-aware datetime values into the database. This value: '2019-08-11 13:00:00+00:00' was not stored using Pony. Correct value should be '2019-08-11 13:00:00.000000'. As datetime values stored as strings and compared as strings, you can't correctly compare as string values '2019-08-11 13:00:00+00:00' and, say, '2019-08-11 12:00:00+01:00'

Hmm, I convert the datetime to utc before saving it into the db

So that's the issue?

I'll try to debug the timestamp before storing it :D

I store it with pony

I could try to just store it without converting it to utc

I want the datetime to be machine-independend though

Hmm, this looks like a bug that Pony does not check that datetime value is not timezone-naive #todo

Cool, it's no problem for now since I'm using raw_sql to make the comparison

But it was confusing me

It is a problem because you can read it back correctly using Pony

I mean for me now

Not that it shouldn't be fixed

Glad I could help find a bug :D

The fix probably be in raising an error when dt.tzinfo is not None

I think you need to do dt = dt.replace(tzinfo=None) before assigning it to attribute And perform the following update to fix values in the database: db.execute(""" update "Match" set "starting_at" = replace("starting_at", '+00:00', '') """)

But when I read it back I'll have the same problem, right?

It will just be a hack after the update