Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?

[Mike Bayer]

Im working on getting off bitbucket but maybe it's an ad blocker? try a
private window or something

On Mon, Oct 8, 2018 at 6:36 PM Lukasz Szybalski  wrote:

>
>
> On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer 
> wrote:
>
>>
>>
>> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski 
>> 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
>>
>
>>
>> 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"}
>> )
>>
>>
>>
>>
>>
>>
>>>
>>>
 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]




> SUPER FAST..now.!!!
> (I wonder what would be a side effect if this was done by default? aka
> user varchar)
>
> For Reference: I'll I split into two lines because for some reason I was
> making some mistake and had to google my way out of it.
>
> Version1 (easier to understand and test)
>
> *from sqlalchemy import text, bindparam,String*
> #  ":mycontract" and "mycontract" parts bound by the bindparams to create
> a statemetn query.
> *# item in bold is what is required to get it to convert from nvarchar to
> varchar*
> stmt= text("select P.contract_id from dbo.P P with(NOLOCK) where
> currentstatus_id in (1,2) and current_cuntract=:mycontract").
> *bindparams(bindparam("mycontract",type_=String(50,convert_unicode='force')))*
> #We now execute the statement from above and bind it to a variable I'm
> passing row.mycontract from our forloop.
> p=session.execute(stmt,params={'mycontract':row.mycontract}).fetchall()
>
> This causes the value to be varchar instead of nvarchar which fixes mssql :
> "NVARCHAR cause performance downgrade because NVARCHAR literals do not
> use VARCHAR indexes. When we add convert_unicode='force' all our
> N'something' turns into 'something' and gains performance from indexes."
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case
>
> **Can you post above as comment to bitbucket ticket. I can't register it
> says "are you sure you are not a robot" with no other option ;) lol . I
> guess they don't get many new accounts lol
>
> Thank you sqlalchemy!
> Lucas
> --
> http://lucasmanual.com/ 
>
>
>
>>
>>>
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>
>
> --
> http://lucasmanual.com/ 
>
> --
> 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.
>

-- 
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 

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?

[Lukasz Szybalski]

On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer  wrote:

>
>
> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski 
> 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
>

>
> 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"}
> )
>
>
>
>
>
>
>>
>>
>>> 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]
>>>
>>>
>>>
>>>
SUPER FAST..now.!!!
(I wonder what would be a side effect if this was done by default? aka user
varchar)

For Reference: I'll I split into two lines because for some reason I was
making some mistake and had to google my way out of it.

Version1 (easier to understand and test)

*from sqlalchemy import text, bindparam,String*
#  ":mycontract" and "mycontract" parts bound by the bindparams to create a
statemetn query.
*# item in bold is what is required to get it to convert from nvarchar to
varchar*
stmt= text("select P.contract_id from dbo.P P with(NOLOCK) where
currentstatus_id in (1,2) and current_cuntract=:mycontract").
*bindparams(bindparam("mycontract",type_=String(50,convert_unicode='force')))*
#We now execute the statement from above and bind it to a variable I'm
passing row.mycontract from our forloop.
p=session.execute(stmt,params={'mycontract':row.mycontract}).fetchall()

This causes the value to be varchar instead of nvarchar which fixes mssql :
"NVARCHAR cause performance downgrade because NVARCHAR literals do not use
VARCHAR indexes. When we add convert_unicode='force' all our N'something'
turns into 'something' and gains performance from indexes."
https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case

**Can you post above as comment to bitbucket ticket. I can't register it
says "are you sure you are not a robot" with no other option ;) lol . I
guess they don't get many new accounts lol

Thank you sqlalchemy!
Lucas
-- 
http://lucasmanual.com/ 



>
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>

-- 
http://lucasmanual.com/ 

-- 
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.

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?

[Mike Bayer]

On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski 
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
> 
>   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 

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?

[Lukasz Szybalski]

> 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? 
# 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 

  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 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.

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql

[Lukasz Szybalski]

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  
>> 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 

  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/ 
>>>
>>> -- 
>>> 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, 

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql

[Lukasz Szybalski]

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  > 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 

  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/ 
>>
>> -- 
>> 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/

Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql

[Mike Bayer]

On Thu, Jan 18, 2018 at 12:31 PM, Lukasz Szybalski 
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




>
> 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/ 
>
> --
> 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.
>

-- 
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.

[sqlalchemy] NVARCHAR vs VARCHAR in mssql

[Lukasz Szybalski]

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?


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/ 

-- 
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.