Let's close this issue. Since pyODBC isn't going to fix anything and claims that SQLAlchemy should use it correctly, Mike's custom set_input_sizes()is the way to solve this problem.
@Mike, having read your description of set_input_sizes(), I guess the reason why Simon's version worked for my single-threaded POC application and failed for my multiprocessing application, is that the POC inserts only a single value (the string), while the other inserts multiple values - sounds right? I will drop the _SC collation until I explicitly needs in my application for string operations, while probably will happen in the future. Thanks Mike and Simon for your great support. On Friday, October 16, 2020 at 10:22:45 PM UTC+2 Mike Bayer wrote: > > > 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 <[email protected]> > 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 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 could perhaps add a SQL Server ruleset directly. If this > were a problem that occurred frequently, then we would begin looking into > turning on some of this behavior by default but we need to be very > conservative on that as this is an area where things break quite a lot. > > > > Below is the recipe that includes a directly vendored version of the > set_input_sizes() hook to suit your immediate use case. that's what I can > get you for now and it will allow you to set the input sizes any way you'd > like. > > > > import pyodbc > > > > from sqlalchemy import Column > > from sqlalchemy import create_engine > > from sqlalchemy import event > > from sqlalchemy import Integer > > from sqlalchemy import String > > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy.orm import Session > > > > > > Base = declarative_base() > > > > e = create_engine( > > > "mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+13+for+SQL+Server", > > echo=True, > > ) > > > > > > @event.listens_for(e, "before_cursor_execute") > > def before_cursor_execute( > > conn, cursor, statement, parameters, context, executemany > > ): > > > > if not hasattr(context.compiled, "bind_names"): > > return > > > > inputsizes = {} > > for bindparam in context.compiled.bind_names: > > # check for the specific datatype you care about here > > if bindparam.type._type_affinity is String: > > inputsizes[bindparam] = ((pyodbc.SQL_WLONGVARCHAR, 0, 0),) > > else: > > inputsizes[bindparam] = None > > > > positional_inputsizes = [] > > for key in context.compiled.positiontup: > > bindparam = context.compiled.binds[key] > > dbtype = inputsizes.get(bindparam, None) > > positional_inputsizes.append(dbtype) > > > > cursor.setinputsizes(positional_inputsizes) > > > > > > class A(Base): > > __tablename__ = "a" > > > > id = Column(Integer, primary_key=True) > > x = Column(Integer) > > data = Column(String) > > y = Column(Integer) > > > > Base.metadata.drop_all(e) > > Base.metadata.create_all(e) > > > > s = Session(e) > > > > s.add(A(data="some data", x=1, y=4)) > > s.commit() > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > 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/f963fe8a-a595-48c5-92a8-597046e199c6n%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/783293d6-6130-449a-a77f-28118ef3ef20%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/CAFHwexfqcGGMsYpn4y192qncs7Ka0a0CssLfdnUHJCoWGdMRCQ%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/c2246a7e-9e30-4eb9-8a9a-fe53e3557651n%40googlegroups.com.
