On Friday, April 27, 2018 at 4:05:20 PM UTC-5, Lukasz Szybalski wrote: > > > > On Thursday, January 18, 2018 at 11:49:39 AM UTC-6, Mike Bayer wrote: >> >> >> >> On Thu, Jan 18, 2018 at 12:31 PM, Lukasz Szybalski <szyb...@gmail.com> >> wrote: >> >>> Hello, >>> 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, Sorry for late response. I'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? Knowing that the value I will be passing is either some sa stored procedure sqlalchemy object.columname or some other system mycontract=someobject.contract_no
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' (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' (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' - What is the final working syntax, I can't find any examples of how to fix 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')) 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? Thank you Lucas __ http://lucasmanual.com > >>> >>> 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] >>> >>> >>> >>> >>> Thank you >>> >>> Lucas >>> >>> >>> >>> >>> -- >>> http://lucasmanual.com/ <http://lucasmanual.com/blog/> >>> >>> -- >>> 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+...@googlegroups.com. >>> To post to this group, send email to sqlal...@googlegroups.com. >>> 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.