Open jmullercuber opened 3 years ago
Right now this project only supports string arrays. However, postgresql supports arrays of many other types [1].
A demonstrative example of this behavior is below:
import enum from sqlalchemy import create_engine, Column, Enum, Integer, String from sqlalchemy.dialects.postgresql import JSONB, ARRAY from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class SockColor(enum.Enum): red = 1 green = 2 black = 3 class User(Base): __tablename__ = "user" id = Column(Integer, primary_key=True) name = Column(String) friends = Column(ARRAY(String)) lucky_numbers = Column(ARRAY(Integer)) # this is not okay docs = Column(ARRAY(JSONB)) # this is not okay socks = Column(ARRAY(Enum(SockColor))) # this is not okay class TestPostgresDialect: engine = create_engine( "postgresql+auroradataapi://:@/TEST_DB_NAME", connect_args=dict( aurora_cluster_arn="TEST_CLUSTER_ARN", secret_arn="TEST_SECRET_ARN" ), ) def test_orm(self): friends = ["Scarlett O'Hara", 'Ada "Hacker" Lovelace'] lucky_numbers = [2, 3, 5, 7, 11, 13, 17, 19] docs = [ {"fizz": [1, 2, 3]}, {"buzz": [4, 5, 6]}, {"bazz": [7, 8, 9]}, ] socks = [SockColor.red, SockColor.red, SockColor.green] Base.metadata.create_all(self.engine) ed_user = User( name="ed", friends=friends, lucky_numbers=lucky_numbers, docs=docs, socks=socks, ) Session = sessionmaker(bind=self.engine) session = Session() # clear data table session.query(User).delete() session.commit() # add record for test user session.add(ed_user) session.commit() # <--- test breaks here # query user record that we just inserted u = session.query(User).filter(User.name.like("%ed")).first() self.assertEqual(u.friends, friends) self.assertEqual(u.lucky_numbers, lucky_numbers) self.assertEqual(u.docs, docs) self.assertEqual(u.socks, socks)
Test should pass
value = [2, 3, 5, 7, 11, 13, ...] def process(value): # FIXME: escape strings properly here > return "\v".join(value) if isinstance(value, list) else value E sqlalchemy.exc.StatementError: (builtins.TypeError) sequence item 0: expected str instance, int found E [SQL: INSERT INTO "user" (name, friends, lucky_numbers, docs, socks) VALUES (:name, string_to_array(:friends, :string_to_array_1), string_to_array(:lucky_numbers, :string_to_array_2), string_to_array(:docs, :string_to_array_3), string_to_array(:socks, :string_to_array_4)) RETURNING "user".id] E [parameters: [{'lucky_numbers': [2, 3, 5, 7, 11, 13, 17, 19], 'docs': [{'fizz': [1, 2, 3]}, {'buzz': [4, 5, 6]}, {'bazz': [7, 8, 9]}], 'name': 'ed', 'friends': ["Scarlett O'Hara", 'Ada "Hacker" Lovelace'], 'socks': [<SockColor.red: 1>, <SockColor.red: 1>, <SockColor.green: 2>]}]]
Inserting just one of the un-supported array types produces similar error messages:
(builtins.TypeError) sequence item 0: expected str instance, SockColor found (builtins.TypeError) sequence item 0: expected str instance, dict found
Thank you for bringing up this idea. At this time I lack the bandwidth to implement it. PRs (with documentation and tests) are welcome.
Right now this project only supports string arrays. However, postgresql supports arrays of many other types [1].
A demonstrative example of this behavior is below:
Expected Result
Test should pass
Actual Result
Inserting just one of the un-supported array types produces similar error messages: