Re: [sqlalchemy] Cannot insert strings with a length greater than 2000 into columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL Server 2017 and 2019
On Fri, Oct 16, 2020, at 10:25 AM, Simon King wrote:
> Yep, I misunderstood what setinputsizes was doing. I thought it told
> pyodbc how it should handle a particular datatype,
that would be great if it worked that way :) however alas...
>
>
> rather than telling
> it how to handle the set of parameters it is about receive in the next
> execute() call...
>
> Sorry for adding to the confusion,
no worries at all.this has to be the first time I've ever seen an
inaccuracy from your part, looking forward to the next one 15 years from now :)
>
> Simon
>
> On Fri, Oct 16, 2020 at 1:14 PM Mike Bayer wrote:
> >
> >
> >
> > On Fri, Oct 16, 2020, at 3:53 AM, Nicolas Lykke Iversen wrote:
> >
> > Is it really necessary to use your very-subtle vendored version of the
> > set_input_sizes() hook? Why use it compared to Simon King's simple version?
> >
> >
> > yes, because cursor.setinputsizes() must be passed an entry for every bound
> > parameter in your statement, in the order that they will be passed to
> > cursor.execute().this includes for all the numerics, dates, etc for
> > which you certainly don't want to pass those as "varchar". so if the
> > third parameter in your statement was the textual version, you'd need to
> > pass cursor.setinputsizes([None, None, (pyodbc.SQL_WVARCHAR, None, None),
> > ...]). Also in my experimenation with this value you want to pass "None"
> > for length, if not otherwise specified, and not 0.
> >
> > Simon's version is hardcoding to passing varchar in all cases for a single
> > bound parameter, and I would not expect that recipe to work at all.
> >
> >
> > Using Simon King's version I experience a weird issue: it works perfectly,
> > when using a single-threaded application, but when using multiprocessing it
> > doesn't work.
> >
> > In particular, if I execute:
> >
> > engine = sqlalchemy.create_engine(
> >
> > f'mssql+pyodbc://{user}:{pwd}@{host}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server',
> >pool_size=5,
> >max_overflow=10,
> >pool_pre_ping=True,
> >isolation_level='READ_UNCOMMITTED',
> >pool_recycle=900,
> >echo=debug,
> >connect_args={'connect_timeout': 10}
> > )
> >
> > @sqlalchemy.event.listens_for(engine, 'before_cursor_execute')
> > def receive_before_cursor_execute(conn, cursor, statement, parameters,
> > context, executemany):
> >cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 0, 0), ])
> >
> > in the main application and:
> >
> > def db_init():
> >engine = common.db.Session.get_bind()
> >engine.dispose()
> >
> > in all the children, then the hook gets called in the children, but somehow
> > doesn't affect the INSERTs - the original error is produced for strings
> > with a. length longer than 2000 characters.
> >
> > Best regards
> > Nicolas
> >
> >
> >
> > On Thursday, October 15, 2020 at 7:39:08 PM UTC+2 Mike Bayer wrote:
> >
> >
> >
> > On Thu, Oct 15, 2020, at 3:26 AM, Nicolas Lykke Iversen wrote:
> >
> > Hi Mike,
> >
> > I have created an issue for pyodbc:
> > https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-708930870
> >
> > I've gotten really good feedback there from Microsoft, and a fix has been
> > proposed that works:
> >
> > "You can try to use setinputsizes on your parameter to tell it to send as
> > varchar(max): cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),])"
> > I'm by no means an SQLAlchemy expert, but shouldn't the pyodbc dialect be
> > updated to support varchar(max)using the proposed method? If not, how can I
> > execute cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),]) using
> > SQLAlchemy, so that I can make use of varchar(max)in my application?
> >
> >
> >
> > SQLAlchemy has some dialects that make use of setinputsizes() out of
> > necessity, but it's an area that is fraught with issues as it means
> > SQLAlchemy is second-guessing what the DBAPI is coming up with.
> >
> > It's actually news to me that pyodbc supports setinputsizes() as
> > historically, the cx_Oracle DBAPI was the only DBAPI that ever did so and
> > this method is usually not supported by any other DBAPI. We have a hook
> > that calls upon setinputsizes() but right now it's hardcoded to cx_Oracle's
> > argument style, so the hook would need alterations to support different
> > calling styles on different dialects.
> >
> > In https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709385941
> > it is suggested that there should be no need to use this "_SC" collation -
> > then in
> > https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709428774,
> > you stated "I previously experimented with non-_SC in my application, and
> > it caused errors.". Can you be more specific of these errors? At the
> > moment, this is suggesting a major architectural rework of the pyodbc
> > dialect to support a use case which has other workarounds. The
> > architecture of SQLAlchemy's set_input_sizes() hook has changed and at best
> > this would be
Re: [sqlalchemy] Cannot insert strings with a length greater than 2000 into columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL Server 2017 and 2019
Yep, I misunderstood what setinputsizes was doing. I thought it told
pyodbc how it should handle a particular datatype, rather than telling
it how to handle the set of parameters it is about receive in the next
execute() call...
Sorry for adding to the confusion,
Simon
On Fri, Oct 16, 2020 at 1:14 PM Mike Bayer wrote:
>
>
>
> On Fri, Oct 16, 2020, at 3:53 AM, Nicolas Lykke Iversen wrote:
>
> Is it really necessary to use your very-subtle vendored version of the
> set_input_sizes() hook? Why use it compared to Simon King's simple version?
>
>
> yes, because cursor.setinputsizes() must be passed an entry for every bound
> parameter in your statement, in the order that they will be passed to
> cursor.execute().this includes for all the numerics, dates, etc for which
> you certainly don't want to pass those as "varchar". so if the third
> parameter in your statement was the textual version, you'd need to pass
> cursor.setinputsizes([None, None, (pyodbc.SQL_WVARCHAR, None, None), ...]).
> Also in my experimenation with this value you want to pass "None" for length,
> if not otherwise specified, and not 0.
>
> Simon's version is hardcoding to passing varchar in all cases for a single
> bound parameter, and I would not expect that recipe to work at all.
>
>
> Using Simon King's version I experience a weird issue: it works perfectly,
> when using a single-threaded application, but when using multiprocessing it
> doesn't work.
>
> In particular, if I execute:
>
> engine = sqlalchemy.create_engine(
>
> f'mssql+pyodbc://{user}:{pwd}@{host}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server',
>pool_size=5,
>max_overflow=10,
>pool_pre_ping=True,
>isolation_level='READ_UNCOMMITTED',
>pool_recycle=900,
>echo=debug,
>connect_args={'connect_timeout': 10}
> )
>
> @sqlalchemy.event.listens_for(engine, 'before_cursor_execute')
> def receive_before_cursor_execute(conn, cursor, statement, parameters,
> context, executemany):
>cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 0, 0), ])
>
> in the main application and:
>
> def db_init():
>engine = common.db.Session.get_bind()
>engine.dispose()
>
> in all the children, then the hook gets called in the children, but somehow
> doesn't affect the INSERTs - the original error is produced for strings with
> a. length longer than 2000 characters.
>
> Best regards
> Nicolas
>
>
>
> On Thursday, October 15, 2020 at 7:39:08 PM UTC+2 Mike Bayer wrote:
>
>
>
> On Thu, Oct 15, 2020, at 3:26 AM, Nicolas Lykke Iversen wrote:
>
> Hi Mike,
>
> I have created an issue for pyodbc:
> https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-708930870
>
> I've gotten really good feedback there from Microsoft, and a fix has been
> proposed that works:
>
> "You can try to use setinputsizes on your parameter to tell it to send as
> varchar(max): cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),])"
> I'm by no means an SQLAlchemy expert, but shouldn't the pyodbc dialect be
> updated to support varchar(max)using the proposed method? If not, how can I
> execute cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),]) using SQLAlchemy,
> so that I can make use of varchar(max)in my application?
>
>
>
> SQLAlchemy has some dialects that make use of setinputsizes() out of
> necessity, but it's an area that is fraught with issues as it means
> SQLAlchemy is second-guessing what the DBAPI is coming up with.
>
> It's actually news to me that pyodbc supports setinputsizes() as
> historically, the cx_Oracle DBAPI was the only DBAPI that ever did so and
> this method is usually not supported by any other DBAPI. We have a hook
> that calls upon setinputsizes() but right now it's hardcoded to cx_Oracle's
> argument style, so the hook would need alterations to support different
> calling styles on different dialects.
>
> In https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709385941 it
> is suggested that there should be no need to use this "_SC" collation - then
> in https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709428774,
> you stated "I previously experimented with non-_SC in my application, and it
> caused errors.". Can you be more specific of these errors? At the moment,
> this is suggesting a major architectural rework of the pyodbc dialect to
> support a use case which has other workarounds. The architecture of
> SQLAlchemy's set_input_sizes() hook has changed and at best this would be
> part of 1.4 which is not in beta release yet, a production release is not for
> several months.
>
> From that point, there's an event hook at do_setinputsizes():
> https://docs.sqlalchemy.org/en/13/core/events.html?highlight=do_setinputsizes#sqlalchemy.events.DialectEvents.do_setinputsizes
>that would be usable so that you could set up rules like these on your
> own, and we eventually would document the known workarounds for various
> unusual issues.
>
> This issue is definitely unusual, it's never been
Re: [sqlalchemy] postgresql JSON(B) dialect: update value of a specific key in a JSON object
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import update
from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(JSONB)
stmt = update(A).values({A.data: A.data + {"key": "value"}}).where(A.id == 5)
print(stmt.compile(dialect=postgresql.dialect()))
On Fri, Oct 16, 2020, at 4:00 AM, Massimiliano della Rovere wrote:
> Greetings,
> how should I write the .values() section of a CORE update() statement to
> render the following
> postgres syntax?
>
> The data column is a JSON(B) and contains a dict object.
>
> UPDATE settings
> SET data = data || '{"key": "value"}'
> WHERE key = 'my_param';
>
> Thanks,
> Massimiliano
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CADKhPGSpo79VpastaH-PJMPQ-WDYFLEkMeCXVhTm0f9U4zJQLw%40mail.gmail.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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/645d7868-2798-4ec6-9d72-a489e4a0e8e9%40www.fastmail.com.
Re: [sqlalchemy] Text subquery column names in the results
On Fri, Oct 16, 2020, at 2:30 AM, Kotofos online wrote:
> I'm working on raw SQL feature, and it has to support distinct and sort.
> Instead of parsing and modifying user query, I'm wrapping it as subquery and
> then do distinct and sort.
>
> ```
> user_query = 'select "FirstName", from "Customer"'
> stmt = text(user_query)
> stmt = select('*').select_from(stmt.columns().alias())
> stmt = stmt.distinct() # and order_by(user_columns)
> print(stmt)
>
> SELECT DISTINCT *
> FROM (select "FirstName", from "Customer") AS anon_1
> ```
>
> So, any better way to implement it? I'm thinking about extracting column
> names from a query and using them in select(). Is it possible in sqlalchemy?
you have to extract the column names from the text if you want to perform
further manipulations with them.
from sqlalchemy import text, select, column
user_query = 'select "FirstName", from "Customer"'
stmt = text(user_query).columns(column("FirstName"))
subq = stmt.alias("subq")
stmt = select([subq])
stmt = stmt.distinct()
stmt = stmt.order_by(subq.c.FirstName)
print(stmt)
> On Thursday, 15 October 2020 at 23:55:53 UTC+7 Mike Bayer wrote:
>>
>>
>> On Thu, Oct 15, 2020, at 2:52 AM, Kotofos online wrote:
>>>
>>> Hi,
>>> Could you shed some light on what I might be doing incorrectly? I have this
>>> text() SELECT * query on top of a one-column sub-query and in the result, I
>>> am not getting that column name.
>>>
>>> ```
>>> stmt = text('select "FirstName", from "Customer"')
>>> stmt = select('*').select_from(stmt.columns().alias())
>>> print(stmt)
>>>
>>> SELECT *
>>> FROM (select "FirstName", from "Customer") AS anon_1
>>> ```
>>>
>>> This is works, but produces incorrect column name in the output:
>>> ```
>>> res = engine.execute(stmt)
>>> keys = res.fetchall()[0].keys()
>>> print(keys)
>>
>> When using select(), the names in the result rows come from those which were
>> provided to the select() method. they are matched to those of the actual
>> cursor if possible but not if they don't match.
>>
>>
>>>
>>> ['*']
>>> ```
>>>
>>> But when the subquery has two columns, then it works as expected:
>>> ```
>>> stmt = text('select "FirstName", "LastName" from "Customer"')
>>> stmt = select('*').select_from(stmt.columns().alias())
>>> res = engine.execute(stmt)
>>> keys = res.fetchall()[0].keys()
>>> print(keys)
>>>
>>> ['FirstName', 'LastName']
>>> ```
>>> So, is there a better way to wrap text query? Why column name is lost in
>>> the first case? Is it a bug?
>>
>> it's basically undefined behavior you're dealing with. you wouldn't want to
>> pass a "*" to select(). I'm sure there's more context to what you actually
>> need to do, but in the example above, there's no need to use a subquery,
>> just invoke the text() construct directly.
>>
>>
>>>
>>>
>>> Tested on 1.3.20 and 1.2.19
>>>
>>> --
>>> 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 view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/c0be052d-1c20-49d1-a73d-875b4a7afef9n%40googlegroups.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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/35fe8997-a400-46cf-9dc7-b4dd86441787n%40googlegroups.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 view this discussion
Re: [sqlalchemy] Cannot insert strings with a length greater than 2000 into columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL Server 2017 and 2019
On Fri, Oct 16, 2020, at 3:53 AM, Nicolas Lykke Iversen wrote:
> Is it really necessary to use your very-subtle vendored version of the
> set_input_sizes() hook? Why use it compared to Simon King's simple version?
yes, because cursor.setinputsizes() must be passed an entry for every bound
parameter in your statement, in the order that they will be passed to
cursor.execute().this includes for all the numerics, dates, etc for which
you certainly don't want to pass those as "varchar". so if the third
parameter in your statement was the textual version, you'd need to pass
cursor.setinputsizes([None, None, (pyodbc.SQL_WVARCHAR, None, None), ...]).
Also in my experimenation with this value you want to pass "None" for length,
if not otherwise specified, and not 0.
Simon's version is hardcoding to passing varchar in all cases for a single
bound parameter, and I would not expect that recipe to work at all.
>
> Using Simon King's version I experience a weird issue: it works perfectly,
> when using a single-threaded application, but when using multiprocessing it
> doesn't work.
>
> In particular, if I execute:
>
> engine = sqlalchemy.create_engine(
>
> f'mssql+pyodbc://{user}:{pwd}@{host}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server',
>pool_size=5,
>max_overflow=10,
>pool_pre_ping=True,
>isolation_level='READ_UNCOMMITTED',
>pool_recycle=900,
>echo=debug,
>connect_args={'connect_timeout': 10}
> )
>
> @sqlalchemy.event.listens_for(engine, 'before_cursor_execute')
> def receive_before_cursor_execute(conn, cursor, statement, parameters,
> context, executemany):
>cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 0, 0), ])
>
> in the main application and:
>
> def db_init():
>engine = common.db.Session.get_bind()
>engine.dispose()
>
> in all the children, then the hook gets called in the children, but somehow
> doesn't affect the INSERTs - the original error is produced for strings with
> a. length longer than 2000 characters.
>
> Best regards
> Nicolas
>
>
>
> On Thursday, October 15, 2020 at 7:39:08 PM UTC+2 Mike Bayer wrote:
>>
>>
>> On Thu, Oct 15, 2020, at 3:26 AM, Nicolas Lykke Iversen wrote:
>>> Hi Mike,
>>>
>>> I have created an issue for pyodbc:
>>> https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-708930870
>>>
>>> I've gotten really good feedback there from Microsoft, and a fix has been
>>> proposed that works:
>>>
>>> *"You can try to use setinputsizes on your parameter to tell it to send as
>>> varchar(max): cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),])"*
>>> I'm by no means an SQLAlchemy expert, but shouldn't the pyodbc dialect be
>>> updated to support varchar(max)using the proposed method? If not, how can I
>>> execute cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),]) using
>>> SQLAlchemy, so that I can make use of varchar(max)in my application?
>>>
>>
>> SQLAlchemy has some dialects that make use of setinputsizes() out of
>> necessity, but it's an area that is fraught with issues as it means
>> SQLAlchemy is second-guessing what the DBAPI is coming up with.
>>
>> It's actually news to me that pyodbc supports setinputsizes() as
>> historically, the cx_Oracle DBAPI was the only DBAPI that ever did so and
>> this method is usually not supported by any other DBAPI. We have a hook
>> that calls upon setinputsizes() but right now it's hardcoded to cx_Oracle's
>> argument style, so the hook would need alterations to support different
>> calling styles on different dialects.
>>
>> In https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709385941
>> it is suggested that there should be no need to use this "_SC" collation -
>> then in
>> https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709428774, you
>> stated "I previously experimented with non-`_SC` in my application, and it
>> caused errors.". Can you be more specific of these errors? At the
>> moment, this is suggesting a major architectural rework of the pyodbc
>> dialect to support a use case which has other workarounds. The
>> architecture of SQLAlchemy's set_input_sizes() hook has changed and at best
>> this would be part of 1.4 which is not in beta release yet, a production
>> release is not for several months.
>>
>> From that point, there's an event hook at do_setinputsizes():
>> https://docs.sqlalchemy.org/en/13/core/events.html?highlight=do_setinputsizes#sqlalchemy.events.DialectEvents.do_setinputsizes
>>that would be usable so that you could set up rules like these on your
>> own, and we eventually would document the known workarounds for various
>> unusual issues.
>>
>> This issue is definitely unusual, it's never been reported and was difficult
>> to find in google searches, so I don't believe we are using pyodbc
>> incorrectly and it would be nice if pyodbc could someday realize that MS SQL
>> Server is the only database anyone really uses their driver with, and they
>>
[sqlalchemy] postgresql JSON(B) dialect: update value of a specific key in a JSON object
Greetings,
how should I write the .values() section of a CORE update() statement to
render the following
postgres syntax?
The data column is a JSON(B) and contains a dict object.
UPDATE settings
SET data = data || '{"key": "value"}'
WHERE key = 'my_param';
Thanks,
Massimiliano
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CADKhPGSpo79VpastaH-PJMPQ-WDYFLEkMeCXVhTm0f9U4zJQLw%40mail.gmail.com.
Re: [sqlalchemy] Cannot insert strings with a length greater than 2000 into columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL Server 2017 and 2019
Thank you, Mike.
pyODBC has the following to say about the issue:
*SQLAlchemy. pyODBC is generic and does not know about special handling of
varchar(max), whereas SQLAlchemy appears to have code for specific database
types. It needs to call setinputsizes as you described, when the length is
more than maximum for non-max types (2K wide characters or 4K bytes).*
Let me be clear, I'm not exactly sure whether omitting _SC is the cause of
the following error, which I have no idea about how to handle:
...
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1124, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1316, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1514, in _handle_dbapi_exception
util.raise_(exc_info[1], with_traceback=exc_info[2])
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
line 182, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1256, in _execute_context
self.dialect.do_executemany(
File
"/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/pyodbc.py",
line 446, in do_executemany
super(MSDialect_pyodbc, self).do_executemany(
File
"/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line
590, in do_executemany
cursor.executemany(statement, parameters)
UnicodeEncodeError: 'utf-16-le' codec can't encode character '\udce5' in
position 11: surrogates not allowed
It complains about UTF-16-LE and surrogates.
However, since _SC is needed for string operations on the SQL Server
backend, don't you think that any real-world application would need it down
the road?
Is it really necessary to use your very-subtle vendored version of the
set_input_sizes() hook? Why use it compared to Simon King's simple version?
Using Simon King's version I experience a weird issue: it works perfectly,
when using a single-threaded application, but when using multiprocessing it
doesn't work.
In particular, if I execute:
engine = sqlalchemy.create_engine(
f'mssql+pyodbc://{user}:{pwd}@{host}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server',
pool_size=5,
max_overflow=10,
pool_pre_ping=True,
isolation_level='READ_UNCOMMITTED',
pool_recycle=900,
echo=debug,
connect_args={'connect_timeout': 10}
)
@sqlalchemy.event.listens_for(engine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, parameters,
context, executemany):
cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 0, 0), ])
in the main application and:
def db_init():
engine = common.db.Session.get_bind()
engine.dispose()
in all the children, then the hook gets called in the children, but somehow
doesn't affect the INSERTs - the original error is produced for strings
with a. length longer than 2000 characters.
Best regards
Nicolas
On Thursday, October 15, 2020 at 7:39:08 PM UTC+2 Mike Bayer wrote:
>
>
> On Thu, Oct 15, 2020, at 3:26 AM, Nicolas Lykke Iversen wrote:
>
> Hi Mike,
>
> I have created an issue for pyodbc:
> https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-708930870
>
> I've gotten really good feedback there from Microsoft, and a fix has been
> proposed that works:
>
>
> *"You can try to use setinputsizes on your parameter to tell it to send as
> varchar(max): cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),])"*
> I'm by no means an SQLAlchemy expert, but shouldn't the pyodbc dialect be
> updated to support varchar(max)using the proposed method? If not, how can
> I execute cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),]) using
> SQLAlchemy, so that I can make use of varchar(max)in my application?
>
>
> SQLAlchemy has some dialects that make use of setinputsizes() out of
> necessity, but it's an area that is fraught with issues as it means
> SQLAlchemy is second-guessing what the DBAPI is coming up with.
>
> It's actually news to me that pyodbc supports setinputsizes() as
> historically, the cx_Oracle DBAPI was the only DBAPI that ever did so and
> this method is usually not supported by any other DBAPI. We have a hook
> that calls upon setinputsizes() but right now it's hardcoded to cx_Oracle's
> argument style, so the hook would need alterations to support different
> calling styles on different dialects.
>
> In https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709385941
> it is suggested that there should be no need to use this "_SC" collation -
> then in
> https://github.com/mkleehammer/pyodbc/issues/835#issuecomment-709428774,
> you stated "I previously experimented with non-_SC in my application, and
> it caused errors.". Can you be more specific of these errors? At the
> moment, this is
Re: [sqlalchemy] Text subquery column names in the results
I'm working on raw SQL feature, and it has to support distinct and sort.
Instead of parsing and modifying user query, I'm wrapping it as subquery
and then do distinct and sort.
```
user_query = 'select "FirstName", from "Customer"'
stmt = text(user_query)
stmt = select('*').select_from(stmt.columns().alias())
stmt = stmt.distinct() # and order_by(user_columns)
print(stmt)
SELECT DISTINCT *
FROM (select "FirstName", from "Customer") AS anon_1
```
So, any better way to implement it? I'm thinking about extracting column
names from a query and using them in select(). Is it possible in sqlalchemy?
On Thursday, 15 October 2020 at 23:55:53 UTC+7 Mike Bayer wrote:
>
>
> On Thu, Oct 15, 2020, at 2:52 AM, Kotofos online wrote:
>
>
> Hi,
> Could you shed some light on what I might be doing incorrectly? I have
> this text() SELECT * query on top of a one-column sub-query and in the
> result, I am not getting that column name.
>
> ```
> stmt = text('select "FirstName", from "Customer"')
> stmt = select('*').select_from(stmt.columns().alias())
> print(stmt)
>
> SELECT *
> FROM (select "FirstName", from "Customer") AS anon_1
> ```
>
> This is works, but produces incorrect column name in the output:
> ```
> res = engine.execute(stmt)
> keys = res.fetchall()[0].keys()
> print(keys)
>
>
> When using select(), the names in the result rows come from those which
> were provided to the select() method. they are matched to those of the
> actual cursor if possible but not if they don't match.
>
>
>
> ['*']
> ```
>
> But when the subquery has two columns, then it works as expected:
> ```
> stmt = text('select "FirstName", "LastName" from "Customer"')
> stmt = select('*').select_from(stmt.columns().alias())
> res = engine.execute(stmt)
> keys = res.fetchall()[0].keys()
> print(keys)
>
> ['FirstName', 'LastName']
> ```
> So, is there a better way to wrap text query? Why column name is lost in
> the first case? Is it a bug?
>
>
> it's basically undefined behavior you're dealing with. you wouldn't want
> to pass a "*" to select(). I'm sure there's more context to what you
> actually need to do, but in the example above, there's no need to use a
> subquery, just invoke the text() construct directly.
>
>
>
> Tested on 1.3.20 and 1.2.19
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/c0be052d-1c20-49d1-a73d-875b4a7afef9n%40googlegroups.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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/35fe8997-a400-46cf-9dc7-b4dd86441787n%40googlegroups.com.
