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 <mik...@zzzcomputing.com> 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 sqlalchemy+...@googlegroups.com. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/sqlalchemy/ca549391-4360-480e-8c58-06577f6d92dan%40googlegroups.com?utm_medium=email&utm_source=footer>. >>>> >>>> >>>> -- >>>> 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 >>>> sqlalchemy+...@googlegroups.com. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.com >>>> >>>> <https://groups.google.com/d/msgid/sqlalchemy/b4a135ef-1c1e-476c-a3f0-60326dca76ea%40www.fastmail.com?utm_medium=email&utm_source=footer>. >>> >>> -- >>> 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/CAE%2B5CJABrO_459MHtDePMXah8mb67TFvQB8rUwgVgk6%2By4v-ow%40mail.gmail.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/CAE%2B5CJABrO_459MHtDePMXah8mb67TFvQB8rUwgVgk6%2By4v-ow%40mail.gmail.com?utm_medium=email&utm_source=footer>. >> > > -- > 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/60f82633-887a-43a3-ac52-c5541058e0bcn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/60f82633-887a-43a3-ac52-c5541058e0bcn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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/1f65e2d7-73cb-46f2-bf2c-ababcaa6152b%40www.fastmail.com.