from sqlalchemy import *
import datetime
db = create_engine('sqlite:///test.db')
metadata = MetaData(db)
trs_transaction = Table(
"trs_transaction", metadata,
Column('transaction_id', Integer(), primary_key=True, autoincrement=True),
Column('account', BigInteger(), index=True, nullable=True),
Column('cost_center', BigInteger(), index=True),
Column('invoice_date', Date()),
Column('trust_status', Enum('open', 'closed',
name='trs_transaction__trust_status')),
Column('interest', DECIMAL(precision=16, scale=2), nullable=True),
Column('balance', DECIMAL(precision=16, scale=2), nullable=True),
Column('current_balance', DECIMAL(precision=16, scale=2), nullable=True),
)
cost_center = 1
invoice_date = datetime.date(2013, 5, 10)
subq = select([
func.max(trs_transaction.c.invoice_date).label('max_invoice_date'),
trs_transaction.c.account,
trs_transaction.c.cost_center
]).where(trs_transaction.c.cost_center == cost_center).\
where(trs_transaction.c.invoice_date <= invoice_date).\
group_by(trs_transaction.c.account).alias('x')
m = trs_transaction.alias('m')
stmt = select([
m.c.account,
m.c.invoice_date,
m.c.trust_status,
m.c.interest,
m.c.cost_center,
m.c.current_balance
]).select_from(subq.join(
m, and_(
subq.c.max_invoice_date == m.c.invoice_date,
subq.c.account == m.c.account,
subq.c.cost_center == m.c.cost_center,
m.c.current_balance > 0
)
)
)
print stmt
On Sep 15, 2014, at 7:49 PM, Mike Bernson <[email protected]> wrote:
> Here is simple table def
>
> from sqlalchemy import *
>
> db = create_engine('sqlite:///test.db')
> metadata = MetaData(db)
>
> trs_transaction = Table("trs_transaction", metadata,
> Column('transaction_id', Integer(), primary_key=True, autoincrement=True),
> Column('account', BigInteger(), index=True, nullable=True),
> Column('cost_center', BigInteger(), index=True),
> Column('invoice_date', Date()),
> Column('trust_status', Enum('open', 'closed',
> name='trs_transaction__trust_status')),
> Column('interest', DECIMAL(precision=16, scale=2), nullable=True),
> Column('balance', DECIMAL(precision=16, scale=2), nullable=True),
> Column('current_balance', DECIMAL(precision=16, scale=2), nullable=True),
> Index('trs_transaction__invoice_date', 'invoice_date', 'account',
> 'cost_center', unique=True),
> )
>
> metadata.create_all()
>
>
> On 09/15/2014 07:04 PM, Michael Bayer wrote:
>> if you want to send (simplified, succinct) table defs i can show you how to
>> get that in core.
>>
>>
>> On Sep 15, 2014, at 6:39 PM, Mike Bernson <[email protected]> wrote:
>>
>>> Given the table below I am trying to get the where statement built
>>> using just core. I have tried a number of thing and am not able to
>>> get the query built.
>>>
>>> I can get the query close but not correct. I must be missing something
>>> simple.
>>>
>>> The database is Mysql and SQLAchemy is 9.7
>>>
>>> The query does work correct when used as text statement.
>>>
>>> CREATE TABLE `trs_transaction` (
>>> `transaction_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>>> `account` bigint(20) unsigned DEFAULT NULL,
>>> `cost_center` bigint(20) unsigned NOT NULL,
>>> `invoice_date` date NOT NULL,
>>> `trust_status` enum('open','closed') NOT NULL,
>>> `interest` decimal(16,2) DEFAULT NULL,
>>> `balance` decimal(16,2) DEFAULT NULL,
>>> `current_balance` decimal(16,2) DEFAULT NULL,
>>> PRIMARY KEY (`transaction_id`),
>>> UNIQUE KEY `invoice_date` (`invoice_date`,`account`,`cost_center`),
>>> KEY `account` (`account`),
>>> KEY `cost_center` (`cost_center`),
>>> ) ENGINE=InnoDB;
>>>
>>>
>>> where = "select m.account, m.invoice_date, m.trust_status, " \
>>> "m.interest, m.cost_center, m.current_balance from \n" \
>>> "(select max(invoice_date) as max_invoice_date, account, "\
>>> "cost_center from trs_transaction where cost_center = %s and " \
>>> " invoice_date <= '%s' group by account) as x \n" \
>>> "inner join trs_transaction as m on \n" \
>>> "x.max_invoice_date = m.invoice_date and x.account = m.account " \
>>> "and m.cost_center = x.cost_center and " \
>>> "m.current_balance > 0" % (self.cost_center, self.invoice_date)
>>>
>>> --
>>> 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
