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 <[email protected]>
>> 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 [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.
