Inner Joins in sqlalchemy example?

[Lukasz Szybalski]

Hello,

I'm trying to make this inner join in sqlalchemy. MSSql.

Is there an example of this? or a guide how to reproduce this. AutoLoad All tables.

I need to know how to create this in sqlalchemy, then I'm going to loop the csv file with 1000 records and make below query.

Use MyDabase

Select
CI.check_id,
c.check_number,
c.checkstatus_id,
c.total_amount,
cc.claim_number,
c.clearedbank_date

from dbo.Checks C with (NOLOCK)
Inner Join dbo.Item CI with (NOLOCK)
on C.check_id = CI.check_id
Inner Join dbo.Control CC with (NOLOCK)
on CC.control_id = CI.ontrol_id
inner Join dbo.StateLob CSL with (NOLOCK)
on CSL.statelob_id = CI.statelob_id
inner join dbo.LOB CL with (NOLOCK)
on CL.lob_id = CSL.lob_id

where c.check_number = 1234
and c.total_amount = 54.55

In prior version of SA I've done:
import sqlalchemy
e=sqlalchemy.create_engine("mssql+pyodbc://Myusername:Mypassword@SQLServer2014Test")
e.echo=False
metadata=sqlalchemy.MetaData(e)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=True)
session = Session()

from sqlalchemy.orm import mapper

checks_table = sqlalchemy.Table('dbo.checks', metadata, autoload=True)
class checks(object):
    pass

mapper(checks,checks_table)

Thanks

Lucas

[Mike Bayer]

this will look something like (assuming Core):

select([CI.c.check_id c.c.check_number, ...]).select_from(
Checks.join(Item, Checks.c.check_id==CI.c.check_id)
.join(Control, Control.c.control_id == Ci.c.control_id)
...

).with_hint(Checks, "WITH (NOLOCK)")
.with_hint(Control, "WITH (NOLOCK)")
...

with_hint is available on Query as well.

SQL Server dialect is lacking documentation to point out the
"with_hint()" generic feature for use with NOLOCK.

>
>
>
> In prior version of SA I've done:
> import sqlalchemy
> e=sqlalchemy.create_engine("mssql+pyodbc://Myusername:Mypassword@SQLServer2014Test")
> e.echo=False

> *metadata=sqlalchemy.MetaData(e)*

>
> from sqlalchemy.orm import sessionmaker
> Session = sessionmaker(bind=e, autoflush=True, autocommit=True)
> session = Session()
>
> from sqlalchemy.orm import mapper
>

> *checks_table = sqlalchemy.Table('dbo.checks', metadata, autoload=True)
> class checks(object):
> pass
>
> mapper(checks,checks_table)
> *
>
> Thanks
> Lucas
>
>
> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.