Thank you for noticing this, I'll fix it

Got a problem with datetimes and timedelta. I want to compare the time when the cart was last updated to a the time in seconds in which the items in the cart are reserved for the customer. This are 2 of my tries: valid_carts = Product[1].cart_items.filter( lambda ci: (datetime.now() - ci.cart.last_updated) <= timedelta(seconds=cart_reservation_time) )

Or: valid_carts = Product[1].cart_items.filter( lambda ci: (datetime.now() - ci.cart.last_updated).total_seconds() <= cart_reservation_time )

Also tried to use timestamp() on both datetimes, but nothing worked. With the first i get NotImplementedError: timedelta(seconds=cart_reservation_time)

With the second: AttributeError: 'timedelta' object has no attribute 'total_seconds': datetime.now() - ci.cart.last_updated.total_seconds

And with timestamp I get: AttributeError: 'datetime' object has no attribute 'timestamp': ci.cart.last_updated.timestamp

Try to use the first approach and define the timedelta value outside of the query: td = timedelta(seconds=cart_reservation_time) valid_carts = Product[1].cart_items.filter( lambda ci: (datetime.now() - ci.cart.last_updated) <= td )

Try to use the first approach and define the timedelta value outside of the query: td = timedelta(seconds=cart_reservation_time) valid_carts = Product[1].cart_items.filter( lambda ci: (datetime.now() - ci.cart.last_updated) <= td )

Cool! Seems to work! You're great! So you need to use this datetime stuff outside of Pony?

It is supposed to work in queries as well, but as NotImplementedError shows, the constructor of timedelta with seconds keyword argument was not properly implemented yet

I hope we can fix it in an upcoming release

Cool!

good morning, trying to use sqlite extensions like fts5 and spatialite, but can't find documentation/examples

Suppose you have an entity class MyEntity(db.Entity): id = PrimaryKey(int) title = Required(str) description = Required(str) ... After you created the database and tables, you can execute an additional commands like the following: sql_create_fts_table = """ CREATE VIRTUAL TABLE IF NOT EXISTS FtsIndex USING FTS5 (title, description, content='MyEntity', tokenize='porter unicode61 remove_diacritics 1');""" sql_add_fts_trigger_insert = """ CREATE TRIGGER IF NOT EXISTS fts_ai AFTER INSERT ON MyEntity BEGIN INSERT INTO FtsIndex(rowid, title, description) VALUES (new.rowid, new.title, new.description); END;""" sql_add_fts_trigger_delete = """ CREATE TRIGGER IF NOT EXISTS fts_ad AFTER DELETE ON MyEntity BEGIN DELETE FROM FtsIndex WHERE rowid = old.rowid; END;""" sql_add_fts_trigger_update = """ CREATE TRIGGER IF NOT EXISTS fts_au AFTER UPDATE ON MyEntity BEGIN DELETE FROM FtsIndex WHERE rowid = old.rowid; INSERT INTO FtsIndex(rowid, title) VALUES (new.rowid, new.title, new.description); END;""" with db_session(ddl=True): cursor = self.db.get_connection().cursor() cursor.execute(sql_create_fts_table) cursor.execute(sql_add_fts_trigger_insert) cursor.execute(sql_add_fts_trigger_delete) cursor.execute(sql_add_fts_trigger_update) and then in your query you can do something like fts_query = "foo bar*" select( obj for obj in MyEntity if raw_sql(""" id in SELECT(rowid from FtsIndex WHERE FtsIndex MATCH $fts_query) """) ) Can't say about spatialite, I haven't had experience with it

thank you, would see if can create some functions to simplify the use. by the way, there is a typo in the query, it should be id in (SELECT rowid from FtsIndex WHERE FtsIndex MATCH $fts_query) (note the parenthesis)

you are right :)

https://gist.github.com/ne555/2a14c257ee82f33b110daf16b28c3ae2

the class would need to fill the parameters of generate_fts() and forward the search_fts() call. (just one fts index per class)

Looks viable. The _search_fts query misses the WHERE condition (or maybe you plan to pass it as a part of the argument)

FROM {table}_fts($value) that's equivalent to FROM {table}_fts WHERE {table}_fts MATCH $value

perhaps change it to the second form

How long for python 3.12 support? 🤭

I hope not as long as for 3.11 :)

At the first glance 3.12 is supported as well.

We need to handle the JUMP_BACKWARD opcode; without it, the support of Python 3.11 is incomplete. I plan to make a new release soon for that reason. For 3.12, there are a bunch of new opcodes, but I hope it should not be too hard to add support for them.

good morning, how to rebind the db? tried to do db.disconnect() but when binding again it raise the error <class 'pony.orm.core.BindingError'> Database object was already bound to SQLite provider

binding is not connecting

binding means "create tables for me for this specific database"

ok, so how to connect?

If your method or function is decorated with @db_session - pony will connect by itself

I have several .db files (all have the same tables), have to change to which .db file want to connect

you can import db object from file

from pony import orm db = orm.Database() class TestEntity(db.Entity): name = orm.Required(str) db.bind( 'sqlite', filename='¿?' # not defined yet ) # all the db have the same tables and are already populated db.generate_mapping(create_tables=False) def foo(file): db.connect(file) with orm.db_session: return TestEntity.get(name='asdf') # want to connect to different databases foo('a.db') foo('b.db') foo('c.db')

how to implement foo() so can change the .db file to load

remove db.connect from function

Or pass db as parameter to foo

I only have one db object that want to change the file that it refers to

or should have db_a, db_b, db_c and generate the mapping for each one?

db1 = orm.Database("sqlite", ...) db2 = orm.Database("postgres", ...) db3 = orm.Database("mysql", ...) def define_entities(db): class TestEntity(db.Entity): name = orm.Required(str) db.generate_mapping(...) define_entities(db1) define_entities(db2) define_entities(db3) def get_by_name(db, name): with orm.db_session: return db.TestEntity.get(name=name)

anyone have any luck integrating pony orm with strawberry to make graphql apis?

I have any issue in Querying the for JSON filed. Here is my entity defined. class Contract(database.Entity): _table_ = 'odm_contract' contract_id = orm.PrimaryKey(str) contract_name = orm.Required(str) application_id = orm.Required(str) contract_details = orm.Optional(orm.Json) Query is as below. sql = """ SELECT * FROM odm_contract WHERE application_id = "0oa9ldovf85vOE11L0h7" and JSON_CONTAINS(contract_details, '{"name": "0"}', '$') """ contracts = database.execute(sql) And I get error as below. contracts = database.execute(sql) File "/opt/python/pony/orm/core.py", line 870, in execute return database._exec_raw_sql(sql, globals, locals, frame_depth=cut_traceback_depth+1, start_transaction=True) File "/opt/python/pony/orm/core.py", line 880, in _exec_raw_sql adapted_sql, code = adapt_sql(sql, provider.paramstyle) File "/opt/python/pony/orm/core.py", line 238, in adapt_sql try: expr, _ = parse_expr(sql, i+1) File "/opt/python/pony/utils/utils.py", line 270, in parse_expr if match is None: raise ValueError() ValueError

Can someone help me please, the same query works with MySQL. @akozlovsky and Others please need to quick solution for this

@all I would be interested to know what's worng here. And contract details here will have an array of Json object and name is one of key. I had implemented as below. contracts = orm.select(cd for cd in Contract if cd.application_id == application_id) menu_map = {constants.NAME: menu_name} sql = "JSON_CONTAINS(contract_details, '{0}')".format(json.dumps(menu_map)) contracts = contracts.where(lambda: orm.raw_sql(sql)) It would work, except for special chars $ and % in name value

Hi Santosh, what database do you use?

I use MySQL

You need to use $$ instead of $, as $ has a special meaning in PonyORM raw queries. In the resulted SQL query, $$ will be replaced with a single $

How about %

Input can be any string so do you recommend to replace $ with $$ and % with $% if come across these chars anywhere in the string

In PonyORM raw SQL queries, single $ is used for parameters, like $x or $(a + b) Regarding %, I do not remember, it depends on a lower level mySQL driver and the way it handles params. If % is used inside a string literal (similar to '$' in string literal in your query), then probably it also should be replaced with %%.

Input can be any string so do you recommend to replace $ with $$ and % with $% if come across these chars anywhere in the string

That means that you basically receives the text of the SQL query from the outside. It is dangerous and can lead to an SQL injection Usually the text of SQL query should be defined in the application, and only param values should be received from the outside

If what you get from the outside is not an arbitrary SQL query fragment, but just a name, then you can check with a regex that it is indeed a name and nothing else, and replace % and $ with %% and $$

If what you get from the outside is not an arbitrary SQL query fragment, but just a name, then you can check with a regex that it is indeed a name and nothing else, and replace % and $ with %% and $$

That did the trick

That means that you basically receives the text of the SQL query from the outside. It is dangerous and can lead to an SQL injection Usually the text of SQL query should be defined in the application, and only param values should be received from the outside

No there is validation check, but I cant have any character restrictions, also it won’t be unknown data

hi, good morning

need help, please!

with this error

Use Set, not set when defining usuariomenudet

thanks!

db1 = orm.Database("sqlite", ...) db2 = orm.Database("postgres", ...) db3 = orm.Database("mysql", ...) def define_entities(db): class TestEntity(db.Entity): name = orm.Required(str) db.generate_mapping(...) define_entities(db1) define_entities(db2) define_entities(db3) def get_by_name(db, name): with orm.db_session: return db.TestEntity.get(name=name)

good morning, sorry for the late reply (don't work outside work hours). although that would work, it generates the mapping over and over again, don't see the need for that as I don't change the provider (dunno if it is expensive).

db.generate_mapping should be called once

about define_entities(db), have all my models in separate files. cannot do def define_entities(db): from . import models

db object has models references inside it

db.generate_mapping should be called once

each time you call define_entities() there would be a call to generate_mapping(). I know that they are different objects, my point is that the mapping is the same.

you call define_entities once

If you run generate_mapping without create_tables=True it should be pretty fast, it just creates table definitions in memory and checks that tables with the specified names are indeed present in the database

ok, would create some factories... as an alternative, what's the worst that would happen if simply do db.provider = None and then bind to the other file

Are all of your databases the SQLite databases with exactly the same structure?

Is your code single-threaded or multi-threaded?

yes, sqlite, same structure. multi-threaded

Switching the db file isn't the supported behavior, but you can try: 1. After you send queries to the database, it keeps open connections in the connection pool, one connection for each thread from where the database was used. Before switching to a different database file, you should do db.disconnect() from each thread that worked with the database before. Otherwise, you'll reuse the connection to a previous database file when working with a new database file. 2. If you performed db.disconnect() from all threads, you can do db.provider.pool.filename = new_filename to switch to a different database file, and after that, Pony should work with a new database. Pony will expect the new database contains proper tables

great, thank you. will test it later

any solution for migration.?

Is it possible to do a "in" search in the integrity of a JSON column? Or a way to cast the JSON column to string and then do that "in" query?

Is it possible to do a "in" search in the integrity of a JSON column? Or a way to cast the JSON column to string and then do that "in" query?

What do you mean? Like, obj for obj in MuEntity if item in obj.json_array_column and obj for obj in MyEntity if key in obj.json_dict?

Yes exactly, it seems that in only works if I specify a key to match in, and even then it only works with exact match. Like obj for obj in MyEntity if search_string in obj.json_column['name']

I have columns with {"name": "John"} but if I have the search string being Jo it does not find them, it only works if it is John

What database do you use?

And obj for obj in MyEntity if search_string in obj.json_column does not return anything no matter if it's exact match or not

I use Postgres 14

Ok actually if I cast with str() it seems to work. Like obj for obj in MyEntity if search_string in str(obj.json_column)