3

I want to store organization data in PostgreSQL database using Enums. Since organization names sometimes change, I need to sometimes update the Enum values, and I want to automatize this using Python with sqlalchemy and psycopg2. However after altering the Enum, new values cannot be inserted.

I suspect that this is due to internal check() method of psycopg2, since the database do accept the new value. Do you know a method to update the types known by psycopg2?

Here are my tests for this script:

class DbconTestCase(unittest.TestCase):
    def setUp(self):
        self.engine, self.meta = db.get_connection('test_user', 'test_pass', 'testdb')
        # Create test table, which will be deleted later
        self.test_table = Table('test_table', self.meta,
                                Column('id', Integer, primary_key=True),
                                Column('type', Enum('number', 'text', 'image', name='type'), nullable=False),
                                Column('text', String(32)))
        self.meta.create_all()

    def test_add_enum_value(self):
        try:
            # Add new value to enum named 'type'
            db.add_enum_value(self.engine, 'type', 'object')
        except Exception as exp:
            self.assertTrue(False, msg=exp.__cause__)
        else:
            self.assertTrue(True)

    def test_bulk_copy(self):
        types = ['number', 'text', 'image', 'object']
        objects = [{'id': idx,
                    'type': types[idx % len(types)],
                    'text': 'random text to insert'} for idx in range(10000)]
        try:
            db.bulk_copy(self.engine, str(self.test_table.name), objects)
        except Exception as exp:
            self.assertTrue(False, msg=exp.__cause__)
        else:
            self.assertTrue(True)

    def tearDown(self):
        self.meta.drop_all()

And a little explanation: Basically I create a test table in a test db, and then extend an enum type with a new value. Then I try to insert a huge amount of data, with new value of the enum among them. I got the following error:

psycopg2.DataError: invalid input value for enum type: "object"
Hodossy Szabolcs
  • 1,598
  • 3
  • 18
  • 34
  • psycopg2 does not keep track of the enum values it knows. I suspect your test case fails because `test_add_enum_value` is not guaranteed to execute before `test_bulk_copy`. – univerio Apr 20 '17 at 21:17
  • I see, but if execution order varies from run to run, is some cases I would get green runs, however it fails on every run. – Hodossy Szabolcs Apr 21 '17 at 11:30
  • That's an incorrect assumption. If the execution order *is not guaranteed*, then the two functions *can* run in any order; they do not *have to* run in all possible orders. In order words, the order can be arbitrary but stable. – univerio Apr 21 '17 at 16:04
  • I have just realised that setUp and tearDown is run before EVERY test, until now I have thought that setUp is run once, then all tests in the testcase is run, and then tearDown is called. So basically the problem was, that a completely different table object was used to insert, not the one I thought. – Hodossy Szabolcs Apr 24 '17 at 14:33

2 Answers2

2

I also faced this issue, and then I solved that with update the enum value from PostgreSQL directly following this answers, we can also follow this answer to update enum on PostgreSQL directly.

Tri
  • 2,722
  • 5
  • 36
  • 65
0

The enum value in the database should be updated (if you dont want to use migration etc), following sql code helps you

ALTER TYPE type ADD VALUE 'object';
Anıl Şenocak
  • 117
  • 7