additional columns?

When you define new Required or Optional attribute, it requires a new column. When you generated tables at first time, that column was missed

I'm working with an real database in MySQL. The columns exist in the database.

Double check the table and column names in the database

In the error message it says: Unknown column 'faclin2.facl_facc_descripcion' in 'field list'

So this column is not present in the database

no, the column is facl_facc_descripcion in table Faclin

I don't know why the error says facl_facc_descripcion

Because you probably have a typo in entity definition

or upper case vs lower case table name maybe

I removed that column and now I get: pony.orm.dbapiprovider.OperationalError: (1054, "Unknown column 'faclin2.faccab2_facc_id' in 'field list'")

I believe pony expects to only see columns that it knows about

I think is connected with having two keys

for the primary, what do you think about?

Thank you for your time too Matthew

No problem, waiting around for a flight, this is better than the work on my todo list ;)

run this in mysql and show us the output

DESCRIBE faclin2;

ok

Actually, Pony is fine with column which is unknown to Pony, as far as that columns are optional.

At the start Pony makes a select for each table in order to be sure that all columns described in entity are really exists

It seems that some attributes are descibed in Entity, but does not have corresponding column in the table

MariaDB [clp_pruebas2]> describe faclin2; +------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------+-------+ | facl_facc_serie | varchar(10) | NO | PRI | NULL | | | facl_facc_id | int(11) | NO | PRI | NULL | | | facl_alb_id | bigint(20) | NO | PRI | 0 | | | facl_numlinea | int(11) | NO | PRI | 0 | | | facl_fecha | date | YES | | NULL | | | facl_codarticulo | varchar(13) | YES | | NULL | | | facl_descripcion | varchar(54) | YES | | NULL | | | facl_cantidad | float(9,3) | YES | | NULL | | | facl_preciocoste | decimal(11,4) | YES | | NULL | | | facl_precio | decimal(11,4) | YES | | NULL | | | facl_dto | float(4,2) | YES | | 0.00 | | | facl_importe | float(10,3) | YES | | 0.000 | | | facl_iva | tinyint(4) | NO | | 21 | | | facl_talla | char(2) | YES | | NULL | | | facl_color | char(2) | YES | | NULL | | | createdAt | timestamp | YES | | NULL | | | updatedAt | timestamp | YES | | NULL | | +------------------+---------------+------+-----+---------+-------+

+------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------------+------+-----+---------+-------+ | facc_serie | varchar(10) | NO | PRI | NULL | | | facc_id | int(11) | NO | PRI | NULL | | | facc_cli_id | int(11) | NO | MUL | NULL | | | facc_fecha | date | YES | | NULL | | | facc_hora | time | YES | | NULL | | | facc_vencimiento | date | YES | | NULL | | | facc_fechacobro | date | YES | | NULL | | | facc_retencion | float(3,2) | YES | | 0.00 | | | facc_for_id | tinyint(4) | NO | MUL | 0 | | | facc_iva_id | tinyint(4) | NO | MUL | 0 | | | facc_notas | text | YES | | NULL | | | facc_dto | float(9,3) unsigned | YES | | 0.000 | | | facc_re | float(9,3) | YES | | 0.000 | | | createdAt | timestamp | YES | | NULL | | | updatedAt | timestamp | YES | | NULL | | +------------------+---------------------+------+-----+---------+-------+

faccab2

It's a normal master-detail relation between tables with a multiple primary key

Alexander, it's incredible for my I'm chatting with the main developer of Pony 🙂 It's similiar to talk to a famous person for me. Thank you for your time and for your incredible software

Regarding composite keys: If some entity has a composite primary key, then each Required or Optional attribute that rerers to these entity actually implemented using several columns. For example: class Course(db.Entity): name = Required(int) semester = Required(str) PrimaryKey(name, semester) exams = Set('Exam') class Exam(db.Entity): dt = Reqired(date) course = Required(Course) In that example, Exam.course attribute actually adds two columns to Exam table: course_name and course_semester, because they both are needed for making a relationship. If you need to name these columns differently, you can specify correct names in attribute definition: class Exam(db.Entity): dt = Reqired(date) course = Required(Course, columns=['course_name', 'semester'])

class Faccab2(db.Entity): facc_id = Required(int) facc_serie = Required(str) facc_cli_id = Required(int) facc_fecha = Optional(date) faclin2s = Set('Faclin2') PrimaryKey(facc_id, facc_serie) class Faclin2(db.Entity): facl_facc_id = Required(int) facl_facc_serie = Required(str) facl_descripcion = Optional(str) faccab2s = Required(Faccab2, columns=['facc_id', 'facc_serie']) PrimaryKey(facl_facc_id, facl_facc_serie)

I get the error: pony.orm.dbapiprovider.OperationalError: (1054, "Unknown column 'faclin2.facc_id' in 'field list'")

Alexander/Matthew, Is there a better place to write my problem or is this the right channel? I wouldn't like to disturb you both

I think this is the right channel. Instead of faccab2s = Required(Faccab2, columns=['facc_id', 'facc_serie']) you probably need to write faccab2s = Required(Faccab2, columns=['facl_facc_id', 'facl_facc_serie'])

Because you need to specify not column of another table's primary key, but columns of current table which used to make a relationship

I did it and this is the error: pony.orm.core.DBSchemaError: Column 'facl_facc_id' already exists in table 'faclin2'

Then you need to remove facl_facc_id attribute from entity definition

Because it is not an independent attribute, but just a part of composte foreign key

and then I have to remove the primarykey attribute, right?

If I would remove these attribute, I have to remove the primarykey attribute and then I get an error about the 'id primary key'

You probably just need to define it as a single attribute: class Faclin2(db.Entity): faccab2s = PrimaryKey(Faccab2, columns=['facl_facc_id', 'facl_facc_serie'])

I tried it and: pony.orm.core.ERDiagramError: Reverse attribute for Faccab2.faclin2s not found

sorry, this error: pony.orm.dbapiprovider.OperationalError: (1054, "Unknown column 'faclin2.id' in 'field list'")

Try to fiend id primary key

This error Unknown column 'faclin2.id' in 'field list probably appeared if you define Faclin2 entity without any PrimaryKey, and it was added automatically

You need to define primary key manually in way as I desribed here

You probably just need to define it as a single attribute: class Faclin2(db.Entity): faccab2s = PrimaryKey(Faccab2, columns=['facl_facc_id', 'facl_facc_serie'])

Yes, I did it and I get the last error: pony.orm.dbapiprovider.OperationalError: (1054, "Unknown column 'faclin2.id' in 'field list'")

Can you show again Faclin2 definition with which you have got that last error?

class Faclin2(db.Entity): facl_descripcion = Optional(str) PrimaryKey(Faccab2, columns=['facl_facc_id', 'facl_facc_serie'])

MariaDB [clp_pruebas2]> describe faclin2; +------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------+-------+ | facl_facc_serie | varchar(10) | NO | PRI | NULL | | | facl_facc_id | int(11) | NO | PRI | NULL | | | facl_alb_id | bigint(20) | NO | PRI | 0 | | | facl_numlinea | int(11) | NO | PRI | 0 | | | facl_fecha | date | YES | | NULL | | | facl_codarticulo | varchar(13) | YES | | NULL | | | facl_descripcion | varchar(54) | YES | | NULL | | | facl_cantidad | float(9,3) | YES | | NULL | | | facl_preciocoste | decimal(11,4) | YES | | NULL | | | facl_precio | decimal(11,4) | YES | | NULL | | | facl_dto | float(4,2) | YES | | 0.00 | | | facl_importe | float(10,3) | YES | | 0.000 | | | facl_iva | tinyint(4) | NO | | 21 | | | facl_talla | char(2) | YES | | NULL | | | facl_color | char(2) | YES | | NULL | | | createdAt | timestamp | YES | | NULL | | | updatedAt | timestamp | YES | | NULL | | +------------------+---------------+------+-----+---------+-------+

Instead of PrimaryKey(Faccab2, columns=['facl_facc_id', 'facl_facc_serie']) you need faccab2s = PrimaryKey(Faccab2, columns=['facl_facc_id', 'facl_facc_serie'])

You need to define a PrimaryKey attribute with attribute name

pony.orm.core.ERDiagramError: Reverse attribute for Faclin2.faccab2s not found

add class Faccab2(db.Entity): ... faclin2s = Set('Faclin2')

ups, I forgot it

Yes!! I don't get any error now!

Cool :)

I'm going to make some test.

Thank you very very much!!

Sure

It's incredible your support !! and your software!!

😎

Hi guys. I have been doing some tests and I can't get all the rows for the details table from the master object. The summary: I have two models: class Faccab2(db.Entity): facc_id = Required(int) facc_serie = Required(str) facc_cli_id = Required(int) facc_fecha = Optional(date) faclin2s = Set('Faclin2') PrimaryKey(facc_id, facc_serie) class Faclin2(db.Entity): facl_descripcion = Optional(str) faccab2s = PrimaryKey(Faccab2, columns=['facl_facc_id', 'facl_facc_serie']) The Faclin2 table has many items from the Faccab2 (many-to-one). When I run that code, I get the right sqls sentences in debug mode: GET CONNECTION FROM THE LOCAL POOL SELECT c.facc_id, c.facc_serie, c.facc_cli_id, c.facc_fecha FROM faccab2 c WHERE c.facc_id = '101' AND c.facc_serie = 'PS17' SELECT facl_descripcion, facl_facc_id, facl_facc_serie FROM faclin2 WHERE facl_facc_id = %s AND facl_facc_serie = %s [101, u'PS17'] /*this sentence gets all the 3 rows in mysql console*/ But the problem is I’m only getting 1 out of 3 rows in the detail table (Faclin2) when I use the model: facturas = select(c for c in Faccab2 if c.facc_id=='101' and c.facc_serie=='PS17') for factura in facturas: print len(factura.faclin2s) # 1 for linea in factura.faclin2s: print linea.facl_descripcion # I only get one row, the last one I think I'm not using well the query object. What am I doing wrong? How can I get all the detail rows? Thank you in advance.

Your code looks correct. Are SQL queries you got in debug mode was from executing this code?

Yes, they are

It looks strange. Maybe there is some problem with the register of letters? If you execute the query in mysql console, what values of facl_fac_serie do you get? SELECT facl_facc_id, facl_facc_serie FROM faclin2 WHERE facl_facc_id = 101 AND facl_facc_serie = 'PS17' Maybe for some rows it is 'ps17' or something like that?

+--------------+-----------------+ | facl_facc_id | facl_facc_serie | +--------------+-----------------+ | 101 | PS17 | | 101 | PS17 | | 101 | PS17 | +--------------+-----------------+

3 rows

Looks strange. By the way, if you know value of primary key, you can get factura object in a more smple way: factura = Faccab2[101, 'PS17'] but it is irrelevant for this error.

Thank you fro the information

MariaDB [clp_pruebas2]> sELECT facl_facc_id, facl_facc_serie, facl_descripcion FROM faclin2 WHERE facl_facc_id = 101 AND facl_facc_serie = 'PS17'; +--------------+-----------------+--------------------+ | facl_facc_id | facl_facc_serie | facl_descripcion | +--------------+-----------------+--------------------+ | 101 | PS17 | BOLSA REGALO | | 101 | PS17 | BOLSA MEDIANA | | 101 | PS17 | BOLSA COMPLEMENTO | +--------------+-----------------+--------------------+

Well, it is not strange

In model you specified that combination of facl_facc_id and facl_facc_serie is a primary key. But from the data this is not correct - you have multiple rows with the same values of facl_facc_id and facl_facc_serie. You need to specify correct primary key for Faclin2 model

Looks strange. By the way, if you know value of primary key, you can get factura object in a more smple way: factura = Faccab2[101, 'PS17'] but it is irrelevant for this error.

Wow. it's very poweful

ups, I understand. I have another field for the primary key in my database

the line number

So the primary key is not composite?

Is the line number unique?

Yes but there is a missing field for the primary key in the detail table

the primary key in the detail table is facc_id, facc_serie, and facc_numlinea

I forgot to add the facc_numlinea to the model