Custodio remember to create or import all you entities before calling generate mapping and bind

from pony.orm import * from decimal import Decimal db = Database() class Familia(db.Entity): nombre = Required(str, unique=True, index=True) estado = Required(int, sql_default=1) categoria = Set('Categoria') class Categoria(db.Entity): nombre = Required(str, unique=True, index=True) estado = Required(int, sql_default=1) familia = Required(Familia) subcategoria = Set('SubCategoria') class SubCategoria(db.Entity): nombre = Required(str) estado = Required(int, sql_default=1) categoria = Required(Categoria) producto = Set('Productos') class Marca(db.Entity): nombre = Required(str, unique=True, index=True) estado = Required(int, sql_default=1) producto = Set('Productos') class Productos(db.Entity): codigo = Required(str, unique=True, index=True) descripcioncorta = Required(str) descripcionlarga = Optional(str) marca = Required(Marca) modelo = Optional(str) precio = Required(Decimal) descuento = Required(Decimal) costoprom = Optional(Decimal) costoactual = Optional(Decimal) costoanter = Optional(Decimal) existencia = Optional(Decimal) click = Optional(int) subcategoria = Required(SubCategoria) class DBAdmin: def init(self): db.bind(provider='postgres', user='postgres', password='admin', host='localhost', database='bindiy') db.generate_mapping(create_tables=True) @db_session def ver_familias(self): sql = Familia.select(lambda c: c.estado == 1) data = [] for a in sql: row = { 'id': a.id, 'familia': a.nombre, 'estado': a.estado } data.append(row) return data @db_session def ver_familia(self, id_familia): c = Familia(id_familia) @db_session def cargarfamilia(self, familia, estado): data = { 'nombre': familia, 'estado': estado } Familia(**data) if name == 'main': db = DBAdmin()

looks good, but db = DBAdmin() is confusing, you change the type of db variable and make original db object inaccessible

thanks!

now: pony.orm.sqltranslation.IncomparableTypesError: Incomparable types 'Familia' and 'int' in expression: c.familia == int(idfamilia) sql = Categoria.select(lambda c: c.familia == int(idfamilia))

do c.familia.id == int(...

very Thanks!

Hi there! I've installed pony from orm-migrations brunch and it lacks StrArray and other array types. Are they supposed to be replaced with Json?

Hi, orm-migrations branch does not support arrays yet

But from this moment we are focusing on migrations

I want to store a date/timestamp using Pony and SQLite as backend

how shall I define the field?

from datetime import datetime ... class MyEntity(db.Entity): field = Required(datetime)

thansk

It is the first time I am going to use Pony

https://stackoverflow.com/questions/54375414/understanding-memory-consumption-in-ponyorm-with-sqlite I hope you find this question interesting

Any help is welcomed

https://stackoverflow.com/questions/54375414/understanding-memory-consumption-in-ponyorm-with-sqlite I hope you find this question interesting

oh interesnting

i have to sleep but i'll read that tomorrow

(Y)

thanks!

I think I just thanked a hamster bot

one question: storage procedure in ponyorm

What do you want to do ?

Any help is welcomed

AFAIK, this isn’t a memory leak, pony stores objects after commit to provide read-only access to them. To clear cache on every commit wrap a code with ‘db_session(strict=True)’

By the way... Alexander, I have a question about db_session context manager. You told that pony ignores nested db_sessions, but what if inner one have different settings? I.e. outer is a plain db_session and inner is defined like db_session(optimistic=False)? Will it work?

Hi everybody, I'll answer in an hour

AFAIK, this isn’t a memory leak, pony stores objects after commit to provide read-only access to them. To clear cache on every commit wrap a code with ‘db_session(strict=True)’

I will try that, if I understood it right then that will fix the issue

AFAIK, this isn’t a memory leak, pony stores objects after commit to provide read-only access to them. To clear cache on every commit wrap a code with ‘db_session(strict=True)’

I have used with db_session(strict=True): but it still keeps using memory

If Pony Stores objects after commit there is no way to write a procedure for bulk loading like this, as it will exhaust all memory 😰

hi @memmanuel Pony clears cache on leaving the db_session scope here you can see more info on this https://docs.ponyorm.com/transactions.html#working-with-db-session this one When the session ends it does the following actions: - Clears the Identity Map cache

in your code at https://stackoverflow.com/questions/54375414/understanding-memory-consumption-in-ponyorm-with-sqlite you need to move with db_session into the loop

hi @memmanuel Pony clears cache on leaving the db_session scope here you can see more info on this https://docs.ponyorm.com/transactions.html#working-with-db-session this one When the session ends it does the following actions: - Clears the Identity Map cache

I will have to rewrite the code to open a new db_session every each line, moving the db_session inside the loop generates huge performance issues as it would open a db_session per record (I am dealing with 3.5million records)

I will try that thanks a log

Have you considered doing batches of say 10k records?

that has worked well for me with similar projects

I can commit every month of year (I am loading twenty years), I will try the for to split that

why not use pony's page method?

i dont think you need the explicit commit() if you are batching

This is not a memory leak. Pony stores all objects in an im-memory cache in order to implement IdentityMap pattern https://martinfowler.com/eaaCatalog/identityMap.html Each db_session has its own in-memory cache. Inside the cache objects reference each other via relationships, so it is not possible to clear only part of this cache. db_session cache is cleared in two situations: 1) After db_session is over. If you use db_session with strict=True option, the cache is cleared more aggressively 2) After rollback You need to do one of the following 1) Use separate db_session for each chunk of, say, 10000 rows 2) After some number of rows (say, 10000) you need to do commit() to save the changes and then immediately rollback() to clear the cache with db_session(strict=True): for i, row in enumerate(r): <do some work> if i % 10000 == 0: commit() # save the changes rollback() # clear the cache

one question: storage procedure in ponyorm

you can use db.execute(sql) method to call stored procedure

I will try the rollback after commit trick

it seems to work

thanks a lot!

@memmanuel pls see the reply here as well https://stackoverflow.com/questions/54375414/understanding-memory-consumption-in-ponyorm-with-sqlite/54377968#54377968

marked as correct answer, thanks a lot Alexey, so far I am finding PonyORM great

marked as correct answer, thanks a lot Alexey, so far I am finding PonyORM great

👍😀

@metaprogrammer what about my question regarging nested db_session? 🙂 I also have similar question: is there a way to get data from DB without any cache?

Hi Vitaliy! Sorry for late reply! Currently Pony checks inner db_session options, and does one of the following: 1) If inner db_session uses options incompatible with outer db_session (ddl=True or serializable=True), Pony throws an exception 2) for sql_debug option Pony uses new sql_debug option value inside the inner db_session and restores it when returning to the outer db_session 3) other options (strict, optimistic and retry) are ignored In principle it is possible to treat optimistic in the same manner as sql_debug option, that is, take it into account during the inner db_session and restore it back when returning to the outer db_session. But note that exiting from the inner db_session currently does not trigger commit or flush, so changes made in inner db_session may be flushed later in the outer db_session according to the options from the outer db_session. So it may be confusing. So, if we change it in the future, we probably need to force flush changes before entering/exiting inner db_session Some databases support nested transactions. Maybe it is better to change db_session behavior in the future so inner db_session will start nested transaction

Thank you very much for detailed answer. What will happen if rollback() is called inside inner db_session?

Currently it will be applied to the outer db_session, because inner db_session is mainly ignored If we implement suport of nested transactions in the future, theoretically it is possible to rollback changes from the inner db_session only. But it may be very hard to implement, because we need to rollback each in-memory object to its previous state which it has at the beginning of the inner db_session. It may be hard to implement and memory-consuming

OK, and how immediate option is treated in this case?

For inner db_session immediate is ignored too as strict, optimistic and retry

If I have this:class Bar(db_orm.Entity): timeframe = Required(unicode) timezone = Required(unicode) timestamp = Required(datetime) open = Required(Decimal, precision=12, scale=6) high = Required(Decimal, precision=12, scale=6) low = Required(Decimal, precision=12, scale=6) close = Required(Decimal, precision=12, scale=6) volume = Required(Decimal, precision=12, scale=6)

how can I get an equivalent query to: select * from candlestick where date(timestamp)='2018-01-12' limit 10;

select(b for b in Bar if b.datetime.date() == my_date).limit(10)

thanks I found it. it is really simple.

def retrieve_session(self, dt_session): print('DEBUG retrieve_session') print(dt_session) print(dt_session.date()) with db_session(): rs = Bar.select(lambda b: b.timestamp.date() == dt_session.date()) print(rs) print(rs[:]) for r in rs[:]: print(r)

I saw things with db_session and generator but does anyone has some snippets to use pony/db_session in code with async/await ?

Currently Pony does not allow async query execution, but in async frameworks you still can execute usual non-async queries. For simple queries (like, retrieve object by its ID value) it is more efficient than performing query asynchronously, because it avoids context switch. Pony allows to decorate async functions with db_session. When such async function suspends, the db_session suspends too

@app.route("/deco/") @db_session async def deco(request): john = People[1] return JSONResponse({"name": john.name}) def test_read(): client = TestClient(app) response = client.get("/deco/") assert response.status_code == 200 assert response.json() == {"id": 1, "name": "John"}TypeError: 'generator' object is not callable but: @app.route("/read/") async def read(request): with db_session(): john = People[1] return JSONResponse(john.to_dict())work good

Is it flask?

it's a snippet from starllette https://github.com/encode/starlette

it's a snippet from starllette https://github.com/encode/starlette

Permanent link to the encode/starlette project you mentioned. (?)

but I fear the with version blocks. I will test it

@app.route("/deco/") @db_session async def deco(request): john = People[1] return JSONResponse({"name": john.name}) def test_read(): client = TestClient(app) response = client.get("/deco/") assert response.status_code == 200 assert response.json() == {"id": 1, "name": "John"}TypeError: 'generator' object is not callable but: @app.route("/read/") async def read(request): with db_session(): john = People[1] return JSONResponse(john.to_dict())work good

second example is correct IMO

but first looks good

When you use with context manager in async code, you should be extra careful to not call async functions inside the with block, because in Python context manager cannot detect when you switch out of async function and cannot suspend db_session accordingly

If all code inside with context manager is truly synchronous, then it should work good

rs = Bar.select(lambda b: b.timestamp >= dt_session and b.timestamp < dt_session_end) this is one order of magnitude more efficient than using .date()

In this query database can use index on b.timestamp

And in previous query b.timestamp.date was an expression which cannot use index

thanks for the explantion, yes there is an index in place (without the index the performance is terrible)

I have moved from 4 seconds to 0.4 seconds for that query

rs = Customer.select(lambda c: sum(c.orders.price) > 1000)

how can I know the number of results in rs?

len(rs) ehem...

Or query.count() if you don't want actually load all that objects

thanks, good to know, in the particular method I am writing I do not want to actually load the objects

# @db_session async def db_use(): await asyncio.sleep(1) john = People[1] return john.to_dict() async def do_async(func, time): with db_session(): a = await func() return a