On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski <[email protected]> wrote:
> > I have a query in sqlalchemy like below where I lookup contract# in mssql. >>>>> How do I enforce the varchar instead of nvarchar? I tried converting my >>>>> field to "str(mycurrent)" but that didn't do anything. Is there some other >>>>> spot to force VARCHAR to be sent? >>>>> >>>> >>>> >>>> this just came up in >>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case, >>>> in that case they are using typed parameters: >>>> >>>> String(50, convert_unicode='force') >>>> >>>> if you're dealing w/ the raw string like that try this: >>>> >>>> execute(text("select ... where foo = >>>> :mycurrent").bindparams(bindparam("mycurrent", >>>> type_=String(convert_unicode='force'))) >>>> >>>> let me know if that works b.c. this has to be in the docs >>>> >>> >>> >>> >>> > Hello Mike. > > Do you have a minute to guide me on the syntax. I can't figure this out, > and I need to fix this performance problem. If I can't figure it out I'll > just create stored procedures to replace the query. > > > Here is the code again: > > > > # 'm struggling with the syntax here for the bind param. How do I convert > from session.execut(stmt,params) to your bindparams? I can't just cast the > variable I'm passing to correct format? > you need to use the text() construct as I illustrated earlier: session.execute( text("select ... where foo = :mycurrent").bindparams(bindparam("mycurrent", type_=String(convert_unicode='force')), params={"mycurrent": "value"} ) # Keep in mind that the value is either some sqlalchemy object.columname or > some 'other system' variable from a loop aka (for row in First_query: > mycontract=row.contract_no > > #I need to go from to : > >> >> From >> p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock) >> inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D >> with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where >> p.contract=:mycontract and D.srtype_id >> =5",params={'mycontract':str(mycontract)}).fetchall() >> >> To: >> from sqlalchmy import bindparams >> ##mycurrent=someobject.contract_no >> mycurrent='ABC123' >> >> I tried these syntax but it doesn;t work: > > #Incorrect Syntax#1? > >> >> (bad) p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P >> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner >> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num >> where p.contract=:mycontract and D.srtype_id >> =5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall() >> AttributeError: 'TextClause' object has no attribute 'bindparam' >> >> #Incorrect Syntax#2? > >> (bad) p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P >> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner >> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num >> where p.contract=:mycontract and D.srtype_id >> =5").bindparam('mycontract').fetchall() >> A value is required for bind parameter 'mycontract' >> >> >> #Incorrect Syntax #3? > This in the link > <https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case> > indicated. > Page says that a "String(50).with_variant(String(50, > convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't > really say how does somebody converts their variable into correct type > string: > > >> like: >> mycontract=cast(mycontract, String(convert_unicode='force')) >> >> >> #Incorrect Syntax #4? > >> Since I'm passing params right after my statement should the bindparam be >> in params section? >> something along: >> params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')}) >> >> The easiest way would be to convert mycontract into proper >> String(convert_unicode='force') and pass mycontract as in my original query? >> >> >> > > >> You can find that the query that uses NVARCHAR does an index scan has >> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and >> has a total elapsed time of 938 ms. >> >> The query that uses VARCHAR does an index seek and has 7 logical reads on >> the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 ms. >> >> >> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P >> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner >> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num >> where p.current=:mycurrent and D.srtype_id >> =5",params={'mycurrent':str(mycurrent)}).fetchall() >> >> >> >> >> >> [image: Inline image 1] >> >> >> >> > > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
