You could call 'setinputsizes' in a handler for the
'before_cursor_execute' event, something like this:
from sqlalchemy import event
@event.listens_for(SomeEngine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement,
parameters, context, executemany):
cursor.setinputsizes([(pyodbc.SQL_WVARCHAR,0,0),])
https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.ConnectionEvents
https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
Hope that helps,
Simon
On Thu, Oct 15, 2020 at 8:27 AM Nicolas Lykke Iversen <[email protected]> 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?
>
> Can you recommend a hotfix for using varchar(max)in current SQLAlchemy
> applications that need to handle Unicode supplementary characters (_SC)?
>
> I appreciate really appreciate your help.
>
> Best regards
> Nicolas
> On Wednesday, October 14, 2020 at 3:36:11 PM UTC+2 Mike Bayer wrote:
>>
>>
>>
>> On Wed, Oct 14, 2020, at 5:35 AM, Nicolas Lykke Iversen wrote:
>>
>> Hi Mike,
>>
>> I've now tested inserting strings with more than 2000 characters using Azure
>> Data Studio (SQL Server GUI) and everything works.
>>
>> Furthermore, I've tested pyodbc (DBAPI) directly, and it only fails when
>> inserting such strings using parameterised SQL queries (it succeeds without
>> using parametrised queries).
>>
>>
>> that would be expected because all the datatype-related issues occur when
>> bound parameters are passed.
>>
>>
>>
>> You can see my POC below, if you have any interest.
>>
>> I guess it should be submitted as a bug to pyodbc... Do you know if I can
>> disable parametrisation for certain SQL queries in SQLAlchemy?
>>
>>
>> there is not and this is definitely an issue that has to be solved at the
>> pyodbc level, either a bug on their end or something in your configuration
>> that has to be changed.
>>
>>
>>
>>
>>
>> Best regards (and thanks for your help and support!!!)
>> Nicolas
>>
>> System info:
>> python v. 3.8.5
>> pyodbc v. 4.0.30
>> msodbcsql17 v. 17.6.1.1
>>
>> POC:
>> import sys
>> import pyodbc
>>
>> host = 'tcp:127.0.0.1,1433'
>> db = 'pyodbc_test'
>> user = 'sa'
>> pwd = 'P@ssw0rd'
>>
>> print('started')
>>
>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL
>> Server};SERVER='+host+';DATABASE='+'master'+';UID='+user+';PWD='+ pwd,
>> autocommit=True)
>> cursor = cnxn.cursor()
>>
>> try:
>> cursor.execute(f'CREATE DATABASE {db} COLLATE
>> Latin1_General_100_CI_AS_SC')
>> except pyodbc.ProgrammingError as e:
>> pass # database exists
>>
>> cursor.execute(f'USE {db}')
>>
>> try:
>> cursor.execute("""
>> CREATE TABLE msg (
>> id int identity(1,1) not null,
>> content varchar(max) not null
>> );""")
>> except pyodbc.ProgrammingError as exc:
>> pass # table exists
>>
>> content = 2000 * 'A'
>>
>> cursor.execute(f"""
>> INSERT INTO msg (content)
>> VALUES ('{content}')""")
>> print(f'non-param: {len(content)=}: success')
>>
>> sql = f"""
>> INSERT INTO msg (content)
>> VALUES (?)"""
>> cursor.execute(sql, (content))
>> print(f'param: {len(content)=}: success')
>>
>> content = 2001 * 'A'
>>
>> cursor.execute(f"""
>> INSERT INTO msg (content)
>> VALUES ('{content}')""")
>> print(f'non-param: {len(content)=}: success')
>>
>> # this fails!
>> sql = f"""
>> INSERT INTO msg (content)
>> VALUES (?)"""
>> cursor.execute(sql, (content))
>> print(f'param: {len(content)=}: success')
>>
>>
>> #cursor.execute('SELECT * FROM msg')
>> #rows = cursor.fetchall()
>> #for r in rows:
>> # print(r)
>>
>> print('finished')
>>
>>
>>
>> On Wednesday, October 14, 2020 at 12:43:25 AM UTC+2 Mike Bayer wrote:
>>
>>
>>
>> On Tue, Oct 13, 2020, at 4:57 PM, Nicolas Lykke Iversen wrote:
>>
>> Thank you, Mike - very much appreciated!
>>
>> Just to be clear, pyodbc is not a driver, it’s a ODBC-compliant DBAPI,
>> right? I separately downloaded a driver for SQL Server from Microsoft, which
>> pyodbc makes use of.
>>
>>
>> right the pyodbc is the DBAPI in this case, which we usually refer to as a
>> "driver" but in the case of ODBC the "driver" is more specifically the
>> separate ODBC driver component.
>>
>>
>> Do you suggest that changing pyodbc to another SQL Server DPAPI would solve
>> the problem?
>>
>>
>> I suggest that if there is no issue with the query you're running outside of
>> the context of pyodbc that you submit an issue to pyodbc at
>> https://github.com/mkleehammer/pyodbc/issues . However I was able to find a
>> discussion thread about your error message that seemed to be independent of
>> ODBC.
>>
>>
>>
>>
>>
>> If so, can you recommend another DBAPI for SQL Server? Or do you think that
>> the problem is caused by Microsoft’s driver?
>>
>>
>> pyodbc is the only supported driver for SQL Server that exists now for
>> Python. You also definitely want to use Microsoft's ODBC drivers so
>> you're already there.
>>
>>
>>
>> I’m pretty sure SQL Server works fine when accessed using .NET, otherwise
>> the Internet would be full of complaints regarding not being able to insert
>> +2000 characters in a varchar(max).
>>
>>
>> you'd want to see if the same ODBC driver and options are in use in that
>> scenario.
>>
>>
>>
>>
>>
>> Best regards
>> Nicolas
>>
>>
>> On Tue, 13 Oct 2020 at 22.22, Mike Bayer <[email protected]> wrote:
>>
>>
>>
>>
>> On Tue, Oct 13, 2020, at 10:50 AM, Nicolas Lykke Iversen wrote:
>>
>> Hi SQLAlchemy,
>>
>> System information:
>>
>> Mac OS X v. 10.15.7
>> Python v. 3.8.5
>> SQLAlchemy v. 1.3.19
>> MS SQL Server 2017 and 2019 (both Enterprise and Docker images e.g.
>> mcr.microsoft.com/mssql/server:2019-latest)
>>
>> Problem:
>> I have an issue with inserting strings with a length greater than 2000 into
>> columns with a datatype of Text, VARCHAR, or NVARCHAR using MS SQL Server
>> 2017 and 2019.
>>
>> I've checked the MS SQL Server and it creates it column properly with e.g. a
>> datatype of varchar(max)for Text and VARCHAR, which should be able to store
>> strings with a size up to 2 GB according to Microsoft documentation.
>>
>> Furthermore, I've tried using other collations, but I need _SC
>> (supplementary character) support for my applications, so I cannot drop it,
>> and adding _UTF8 (UTF-8) doesn't solve the problem either.
>>
>> Why am I not allowed to store strings with a size greater than 2000?
>>
>>
>> I'm not really sure, this has to do with a behavior of SQL Server and/or
>> your ODBC driver. a google search finds fairly scant results but there is
>> a long discussion regarding this error here:
>> https://www.sqlservercentral.com/forums/topic/collation-error-when-adding-distributer
>>
>>
>>
>>
>>
>>
>>
>>
>> And why is SQLAlchemy displaying that error message, when trying to insert
>> plain ASCII text ("AAAA...")?
>>
>>
>> SQLAlchemy runs SQL statements using a method called "cursor.execute()",
>> which is a feature of the DBAPI (database driver) in use. As your error
>> message indicates you're using the pyodbc driver, this method is documented
>> here:
>> https://github.com/mkleehammer/pyodbc/wiki/Cursor#executesql-parameters
>>
>> The DBAPI execute() method, and most of the other DBAPI methods, can throw
>> exceptions if something goes wrong. SQLAlchemy has the policy that if it
>> encounters such an exception when it calls cursor.execute(), it wraps it in
>> a SQLAlchemy-specific version of that exception (named the same) and then
>> throws it.
>>
>> In other words you're using a driver called pyodbc that's creating this
>> error. SQLAlchemy just propagates it for you but otherwise has nothing to do
>> with how it's produced.
>>
>>
>>
>>
>> I would really appreciate some guidance on how to solve this problem. What
>> could be causing it?
>>
>> Error:
>> sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000',
>> "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot
>> convert to text/ntext or collate to 'Latin1_General_100_CI_AS_SC' because
>> these legacy LOB types do not support UTF-8 or UTF-16 encodings. Use types
>> varchar(max), nvarchar(max) or a collation which does not have the _SC or
>> _UTF8 flags. (4189) (SQLParamData)")
>> [SQL: INSERT INTO msg (content) OUTPUT inserted.id VALUES (?)]
>> [parameters:
>> ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>> ... (1703 characters truncated) ...
>> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',)]
>> (Background on this error at: http://sqlalche.me/e/13/f405)
>>
>>
>> Program (POC):
>> import logging
>> import sqlalchemy
>> from sqlalchemy import Column, Text, Integer, NVARCHAR, VARCHAR
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.orm import sessionmaker
>>
>> logging.root.setLevel(logging.DEBUG)
>>
>> Base = declarative_base()
>>
>>
>> class Msg(Base):
>> __tablename__ = 'msg'
>>
>> id = Column(Integer, primary_key=True, autoincrement=True)
>> content = Column(VARCHAR, nullable=False)
>>
>>
>> user = 'sa'
>> pwd = 'P@ssw0rd'
>> host = 'localhost'
>> port = 1433
>> db = 'test'
>>
>> logging.info('started.')
>>
>> 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=False,
>> connect_args={'connect_timeout': 10})
>>
>> Base.metadata.create_all(engine)
>>
>> Session = sessionmaker(bind=engine, autoflush=False, autocommit=False)
>> session = Session()
>>
>> for i in range(10000):
>> try:
>> msg = Msg(content='A' * i)
>> session.add(msg)
>> session.commit()
>> except Exception as exc:
>> logging.exception(f'fail: {i=}: {exc}')
>> break
>> else:
>> logging.info(f'success: {i=}')
>>
>> logging.info('done.')
>>
>>
>> --
>> 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 view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/Kk6DkPNWlR4/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.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 [email protected].
>>
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJABrO_459MHtDePMXah8mb67TFvQB8rUwgVgk6%2By4v-ow%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 [email protected].
>>
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/60f82633-887a-43a3-ac52-c5541058e0bcn%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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f3e2a277-8529-4fd5-83be-26445616f6c3n%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexf26J3EdOptKCyjD%2BsJF8eUt7bLsZ41vPf%2BbsVKSdBB8A%40mail.gmail.com.
