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? And
why is SQLAlchemy displaying that error message, when trying to insert
plain ASCII text ("AAAA...")?
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.
