Hi all,
I've read documentation and some resources on web but still got something I
can't understand.
I'm using SQLAlchemy 1.3.10 on a mysql db.
When I run this query on dbms I get 219 (unique) rows
SELECT payments.period, SUM(payments.canoneattualizzato) FROM
payments,agreements
WHERE payments.agreements_id = agreements.id group by payments.period ORDER
BY payments.period
When I execute the same query on SQLAlchemy engine I get 72 rows!
sql = text('SELECT payments.period, SUM(payments.canoneattualizzato) FROM
payments,agreements WHERE payments.agreements_id = agreements.id group by
payments.period ORDER BY payments.period')
result = engine.execute(sql)
I arrived to use execute method because I had the exact same issue with
Query object and I thought raw sql could be a fast way to investigate:
resultset = session.query(Payment.period, func.sum(Payment.
canoneAttualizzato).label('canoneAttualizzato')).\
join(Agreement, Agreement.id == Payment.agreements_id).\
group_by(Payment.period).\
order_by(Payment.period)
And this is compiled statement, pretty the same:
SELECT payments.period, sum(payments.`canoneAttualizzato`) AS
`canoneAttualizzato`
FROM payments INNER JOIN agreements ON agreements.id = payments.agreements_id
GROUP BY payments.period ORDER BY payments.period
The rows returned by SQLAlchemy are a subset of the rows returned by dbms
and
they're just in the middle of the full resultset: i mean dbms returns 36
rows above and 111 rows below the SQLAlchemy returned resultset. And
there's apparently no difference between returned and non returned rows (in
terms of primary key and null values).
I can add ORM mapping info:
class Payment(DECLARATIVE_BASE):
__tablename__ = 'payments'
__table_args__ = (
{'mysql_engine': 'InnoDB', 'sqlite_autoincrement': True,
'mysql_charset': 'utf8'}
)
id = Column(INTEGER, autoincrement=True, primary_key=True, nullable=
False) # pylint: disable=invalid-name
period = Column(INTEGER)
anno = Column(INTEGER)
mese = Column(INTEGER)
mensilita = Column(INTEGER)
competenza = Column(DECIMAL)
canoneAttualizzato = Column(DECIMAL)
deltaCanoneAttualizzato = Column(DECIMAL)
valoreContratto = Column(DECIMAL)
valoreCanoneAttualizzato = Column(DECIMAL)
quotaInteressi = Column(DECIMAL)
quotaTotale = Column(DECIMAL)
quotaCapitale = Column(DECIMAL)
quotaAmmortamento = Column(DECIMAL)
PeL = Column(DECIMAL)
quota12Mesi = Column(DECIMAL)
quotaOltre12Mesi = Column(DECIMAL)
amortizations_id = Column(INTEGER, ForeignKey("amortizations.id"), index
=True, nullable=False)
agreements_id = Column(INTEGER, ForeignKey("agreements.id"), index=True,
nullable=False)
#agreement = relationship("Agreement", foreign_keys=[agreements_id],
backref="payments")
#amortization = relationship("Amortization",
foreign_keys=[amortizations_id], backref="payments")
def __repr__(self):
return self.__str__()
def __str__(self):
return "<Payment(%(id)s)>" % self.__dict__
class Agreement(DECLARATIVE_BASE):
__tablename__ = 'agreements'
__table_args__ = (
{'mysql_engine': 'InnoDB', 'sqlite_autoincrement': True,
'mysql_charset': 'utf8'}
)
id = Column(INTEGER, autoincrement=True, primary_key=True, nullable=
False) # pylint: disable=invalid-name
agreementCode = Column(VARCHAR(20), nullable=False)
descrizione = Column(VARCHAR(45))
oda = Column(VARCHAR(15))
deposito = Column(VARCHAR(500))
data_inizio = Column(DATE, nullable=False)
data_fine = Column(DATE, nullable=False)
durata = Column(VARCHAR(100))
isEstinguibileAnticipatamente = Column(TINYINT)
dataMinimaEstinzioneAnticipata = Column(DATE)
dataEstizioneAnticipata = Column(DATE)
canoneTotale = Column(DECIMAL)
canoneServizi = Column(DECIMAL)
canoneDaAttualizzare = Column(DECIMAL, nullable=False)
dataPrimoPagamento = Column(DATE)
dataUltimoPagamento = Column(DATE)
pagamento = Column(VARCHAR(1))
percentualeApplicataTassoRivalutazione = Column(DECIMAL(7,4))
dataApplicazioneRivalutazione = Column(INTEGER)
recesso = Column(VARCHAR(200))
oneriRegistrazione = Column(VARCHAR(200))
oneriAccessori = Column(VARCHAR(200))
polizzaAssicurativa = Column(VARCHAR(200))
autovettureTarga = Column(VARCHAR(10))
autovettureAssegnatario = Column(VARCHAR(50))
note = Column(VARCHAR(500))
mesiFreeRent = Column(INTEGER)
clientVariazione = Column(VARCHAR(20))
userVariazione = Column(VARCHAR(50))
dataVariazione = Column(DATETIME)
isDeleted = Column(TINYINT, default=0, nullable=False)
isClosed = Column(TINYINT, default=0)
isPenaleEstinzioneAnticipata = Column(TINYINT)
importoPenaleEstinzioneAnticipata = Column(DECIMAL)
dataRinnovoAutomatico = Column(DATE)
isFuoriConteggioDurata = Column(TINYINT)
isFuoriConteggioImporto = Column(TINYINT)
currencies_id = Column(INTEGER, ForeignKey("currencies.id"), index=True,
nullable=False)
accounts_id = Column(INTEGER, ForeignKey("accounts.id"), index=True,
nullable=False)
companies_id = Column(INTEGER, ForeignKey("companies.id"), index=True,
nullable=False)
suppliers_id = Column(INTEGER, ForeignKey("suppliers.id"), index=True,
nullable=False)
paymentPeriods_id = Column(INTEGER, ForeignKey("paymentPeriods.id"),
index=True, nullable=False)
countries_id = Column(INTEGER, ForeignKey("countries.id"), index=True,
nullable=False)
discountRates_id = Column(INTEGER, ForeignKey("discountRates.id"), index
=True, nullable=False)
revaluationRates_id = Column(INTEGER, ForeignKey("revaluationRates.id"),
index=True, nullable=False)
agreements_id = Column(INTEGER, ForeignKey("agreements.id"), index=True,
nullable=False)
paymentTypes_id = Column(INTEGER, ForeignKey("paymentTypes.id"), index=
True, nullable=False)
company = relationship("Company", foreign_keys=[companies_id], backref=
"agreements")
revaluationrate = relationship("Revaluationrate", foreign_keys=[
revaluationRates_id], backref="agreements")
currency = relationship("Currency", foreign_keys=[currencies_id],
backref="agreements")
supplier = relationship("Supplier", foreign_keys=[suppliers_id], backref
="agreements")
country = relationship("Country", foreign_keys=[countries_id], backref=
"agreements")
discountrate = relationship("Discountrate", foreign_keys=[
discountRates_id], backref="agreements")
paymentperiod = relationship("Paymentperiod", foreign_keys=[
paymentPeriods_id], backref="agreements")
account = relationship("Account", foreign_keys=[accounts_id], backref=
"agreements")
paymenttype = relationship("Paymenttype", foreign_keys=[paymentTypes_id
], backref="agreements")
agreement = relationship("Agreement", foreign_keys=[agreements_id])
amortizations = relationship("Amortization")
documents = relationship("Document")
def __repr__(self):
return self.__str__()
def __str__(self):
return "<Agreement(%(id)s)>" % self.__dict__
I really can't understand what's going on under the hood and need this
simple query to work (the real one is a bit more complex and with some
where conditions that I removed to simplify the problem)
Can you provide help, please? Thanks in advance.
Have a nice day!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/738f70eb-c5da-41ae-8821-e78f220ef571o%40googlegroups.com.
