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 <m...@mlb.org> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
