SQLAlchemy

Email Archive: sqlalchemy-tickets (read-only)

2006:	_Jan	_Feb	_Mar (174)	_Apr (50)	_May (71)	_Jun (129)	_Jul (113)	_Aug (141)	_Sep (82)	_Oct (142)	_Nov (97)	_Dec (72)
2007:	_Jan (159)	_Feb (213)	_Mar (156)	_Apr (151)	_May (58)	_Jun (166)	_Jul (296)	_Aug (198)	_Sep (89)	_Oct (133)	_Nov (150)	_Dec (122)
2008:	_Jan (144)	_Feb (65)	_Mar (71)	_Apr (69)	_May (143)	_Jun (111)	_Jul (113)	_Aug (159)	_Sep (81)	_Oct (135)	_Nov (107)	_Dec (200)
2009:	_Jan (168)	_Feb (109)	_Mar (141)	_Apr (128)	_May (119)	_Jun (132)	_Jul (136)	_Aug (154)	_Sep (151)	_Oct (181)	_Nov (223)	_Dec (169)
2010:	_Jan (103)	_Feb (209)	_Mar (201)	_Apr (183)	_May (134)	_Jun (113)	_Jul (110)	_Aug (159)	_Sep (138)	_Oct (96)	_Nov (116)	_Dec (94)
2011:	_Jan (97)	_Feb (188)	_Mar (157)	_Apr (158)	_May (118)	_Jun (102)	_Jul (137)	_Aug (113)	_Sep (104)	_Oct (108)	_Nov (91)	_Dec (162)
2012:	_Jan (189)	_Feb (136)	_Mar (153)	_Apr (142)	_May (90)	_Jun (141)	_Jul (67)	_Aug (77)	_Sep (113)	_Oct (68)	_Nov (101)	_Dec (122)
2013:	_Jan (60)	_Feb (77)	_Mar (77)	_Apr (129)	_May (151)	_Jun	_Jul	_Aug	_Sep	_Oct	_Nov	_Dec

[Sqlalchemy-tickets] [sqlalchemy] #2561: ValueError: string data too large when using cx_Oracle and executemany

From: sqlalchemy <mike_mp@zz...> - 2012-09-05 17:36

#2561: ValueError: string data too large when using cx_Oracle and executemany
-------------------------+-----------------------------------------
 Reporter:  Bluehorn     |           Owner:  zzzeek
     Type:  enhancement  |          Status:  new
 Priority:  low          |       Milestone:
Component:  oracle       |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 This is not really a bug in SQLAlchemy but I think SQLAlchemy could work
 around this (most easily by not using executemany for cx_Oracle).

 There is a known problem in cx_Oracle about string buffers in combination
 with executemany: If the first row of a string/text column is smaller than
 4000 bytes, cx_Oracle will allocate a vt_String variable to hold the
 column data. However, if a following row is above that limit, cx_Oracle
 will throw a ValueError("string data too large").

 This is discussed here for example:

  * http://article.gmane.org/gmane.comp.python.db.cx-oracle/2318
  * http://stackoverflow.com/questions/6624991/cx-oracle-executemany-
 with-a-clob

 This appeared to be a regression in SQLAlchemy 0.7.x vs. 0.6.x because it
 seems like SQLAlchemy will now optimize inserts by using executemany. It
 is hard to trace this error, especially as the ORM will only use
 executemany (so it seems to me) if the primary key is already known as is
 the case for joined table inheritance mappers.

 Here is an example using SA core how to trigger this problem:

 {{{
 #!py
 import logging
 from sqlalchemy import *

 logging.basicConfig(level=logging.DEBUG)
 logging.getLogger("sqlalchemy.engine").setLevel(logging.DEBUG)

 engine = create_engine("oracle://lexikon:lexikon@...>")
 connection = engine.connect()

 metadata = MetaData()

 example_table = Table("example", metadata,
         Column("id", Integer, Sequence("id_seq"), primary_key=True),
         Column("info", UnicodeText))

 example_table.create(connection, checkfirst=True)

 values = []
 values.append(dict(info=u"long content" * 4000))
 values.append(dict(info=u"short"))

 # This works: First row has large data, second is smaller.
 connection.execute(example_table.insert(), values)

 # This doesn't ===> ValueError: string data too large, because initial row
 leads to small buffer.
 values.reverse()
 connection.execute(example_table.insert(), values)

 example_table.drop(connection, checkfirst=True)
 }}}

 I actually ran into this using the ORM and was initially unable to
 reproduce. The following example does trigger the problem here, but relies
 on the insert order - I don't know if this it deterministic in SQLAlchemy.

 {{{
 #!py
 import logging
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import *

 logging.basicConfig(level=logging.DEBUG)
 logging.getLogger("sqlalchemy.engine").setLevel(logging.DEBUG)

 Base = declarative_base()

 class ExampleBase(Base):
     __tablename__ = "example_base"
     id = Column(Integer, Sequence("id_seq"), primary_key=True)
     discriminator = Column('type', String(50))
     __mapper_args__ = {'polymorphic_on': discriminator}

 class Example(ExampleBase):
     __tablename__ = "example"
     id = Column(Integer, ForeignKey(ExampleBase.id), primary_key=True)
     info = Column(UnicodeText)
     __mapper_args__ = {'polymorphic_identity': 'example'}


 engine = create_engine("oracle://lexikon:lexikon@...>")
 Base.metadata.create_all(engine)

 Session = sessionmaker(engine)
 session = Session()

 instances = [
     Example(info="short"),
     Example(info="less short " * 4000)
 ]

 for t in instances:
     session.add(t)

 session.commit()
 }}}

 The latter example throws up because both rows to the child table are
 inserted using one executemany call.

 I would like to have an option in SQLAlchemy to disable executemany and
 have SQLAlchemy dissect an executemany request into individual execute
 calls.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2561>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python