You can use triple backquotes, or select and right-click code block and choose correct formatting

Ok, thanks.

```class Stream(db.Entity): stream_id = PrimaryKey(str, sql_type='TEXT') header = Required(Json, sql_type='JSONB', index='stream_header_idx') stables = Set('Stable') experiment = Required(Experiment) measurement = Optional('Measurement') class Measurement(db.Entity): id = PrimaryKey(int, size=64, sql_type='SERIAL', auto=True) time = Required(datetime, sql_type='TIMESTAMPTZ') stream = Required(Stream)

``` this way ```

class Stream(db.Entity): stream_id = PrimaryKey(str, sql_type='TEXT') header = Required(Json, sql_type='JSONB', index='stream_header_idx') stables = Set('Stable') experiment = Required(Experiment) measurement = Optional('Measurement') class Measurement(db.Entity): id = PrimaryKey(int, size=64, sql_type='SERIAL', auto=True) time = Required(datetime, sql_type='TIMESTAMPTZ') stream = Required(Stream)

Oh, the actual class I use is an inherited class. It all ends up in Measurement.

Stream.measurement and Measurement.stream are two sides of the same relationship. When you change one side, the other side changes automatically. If you do stream1.mesaurement = m1 then m1.stream becomes stream1 In a similar way, if you do meas1.stream = stream1 you implicitly do stream1.measurement = meas1

So, when you specify measurement in stream constructor, you link it to this stream. And if that measurement was linked to some other stream before, it will be unlinked out of previous stream

But stream.measurement attribute is required

So you can't implicitly assign prev_stream.measurement = None to previous stream

Other way around. measurement.stream is required, but not stream.measurement.

But same problem...

If you really want to do that, you need to specify Stream.measurement attribute as Optional, so you can unlink measurement object from previous Stream

So how do I get a new instance of Measurement to which I should assign the stream?

I need to create multiple rows in the measurement table. Each row can have a different stream.

But they are all created in the same code location.

If the same measurement should be specified for multiple streams, then probably attribute type should be Set class Measurement: ... streams = Set(Stream) Or you need to create new measurement instance each time when you create a new stream.

You need to ask two independent question to yourself: 1) How many streams can be linked to same measurement object 2) How many measurements can be linked to the same stream object

It's 1-1, but there can be many different kinds of measurements all in the same table using a discrimator column. This is due to using inheritance. X_measurement, Y_measurement, ... all inherit from measurement. But over time, there can be many X_measurements each in different streams.

because these different streams belong to different experiments.

an experiment can have multiple streams, each with a different stream id.

So it's like time series?

experiment 1 can have streams 1, 2, 3 each containing measurement derivatives X, Y and Z

experiment 2 can have streams 4, 5, 6, each having derivatives, X, Y, and Z. So same data types, but different experiments

yes, the measurements are time series. just trying to keep it all straight.

There are many "sensor" sources, each is a stream. stream is just a place to keep the stream metadata with a pointer to the measurement.

ERD for model.

The ERD was developed using the Pony editor.

Sorry for inundating you. I thot it was a pretty simple case of not re-instantiating the Entity object for some reason. Not sure why that isn't happening in the code I posted.

As I understand it, the problem that you discover in line meas = LabInst_Fluke7060_Refridge_Bath(time=record['Timestamp'], BathTemp=record['Temp'], stream=self.db_stream.stream_id) is that you specified the stream that already was linked with different measure, and you cannot broke the link between that stream and the previous measure, because measure.stream is required. If relations between stream and measure is really 1:1, it looks incorrect So probably you need to create both Stream and Measure in pair each time

Ok, I thot that Measurement was just a row in the measurement table? measurement.stream is just the Stream.stream_id. So if I instantiate a new Measurement, then I should have to supply the Stream.stream_id. No?

So, I create the Stream w/o Measurement, because it's optional. Then I create many Measurements supplying Stream, because it's not optional - each Measurement being unique row in table.

When I supply Stream to Measurement Stream.measurement is updated.

Yes, each Measurement instance is just a row in a measurement table, as well as Stream instance is just a row in a stream table. According to what you have said, you want to have the same number of rows in both tables. So yes, when creating new Measurement object you need to specify corresponding new Stream object for that new_stream = Stream(...) new_measurement = LabInst_Fluke7060_Refridge_Bath(..., stream=new_stream)

It is better to specify stream object instead of raw stream_id

Actually, if a database contains 1:1 relationship, in many cases that means that it is possible to combine to entities into a single entity to simplify schema

Oh, sorry, I think I confused rows with entities. :( One Stream can have many Measurement rows. One Measurement can only have one Stream row.

In that case you need to define entities as:

class Stream(db.Entity): ... measurements = Set("Measurement") class Measurement(db.Entity): ... stream = Required("Stream")

Ok, I get it. Thanks so much! You've been a great help!

Sure!

Btw, I'm a complete DB n00b, but even I can see how Pony works the indices behind the scenes to make the relationships work!

Yes, it has pretty complex logic for that :)

Which is exactly why I am using Pony - to avoid all that complexity. Seems like Set is a kind of Optional?

Set is a collection, it can have from zero to any number of related objects

Ok. That did fix things. Thanks again!

Sure

Hi

Does pony have async variant?

Hi, right now pony is intended to be use in synchronous code only

We probalby will add async variant in the future

Ah okay

rip why is this

What web server do you use?

What web server do you use?

Heroku

I use psql

On heroku

What are you developing? Is it web application?

What are you developing? Is it web application?

Telegram Bot

I dont issues when i use sqlalchemy on heroku

Well the issues come when i use pony on heroku

I use this one https://elements.heroku.com/addons/heroku-postgresql

I'm getting a issue with db.generate_mapping(create_tables=True) resulting in pony.orm.dbapiprovider.IntegrityError: duplicate key value violates unique constraint "pg_type_typname_nsp_index"

I dropped the database and created it fresh, so there shouldn't be enything in there...

I had to recreate the database as I forgot that chat ids need to use size=64

Telegram Bot

Welcome to the club!

I'm getting a issue with db.generate_mapping(create_tables=True) resulting in pony.orm.dbapiprovider.IntegrityError: duplicate key value violates unique constraint "pg_type_typname_nsp_index"

Postgres, if not clear.

It seems that the error is arises when several instances of application are trying to create the same tables in parallel Maybe we need to add some lock to pony to prevent simultaneous running of db.generate_mapping from parallel processes

Ah, that's the reason.

There shouldn't be more than one instance… Maybe gunicorn does something like that…

So pony_up: My application with the migration will run multible times at the same time should solve this.

So pony_up: My application with the migration will run multible times at the same time should solve this.

Permanent link to the luckydonald/pony_up project you mentioned. (?)

If they are executed after each other, it should be all fine, yes?

yes

how can I figure out if it needs to run the database inserts?

Run the table creation and catch the specific exception related to it already being created and then continue running your app

Or only run the creation in a setup script that runs once

A query like this isn’t working:

select((x, max(y for y in Y if y.x == x)) for x in X)

I’ve forgotten the trick for this