CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Elektro
Elektro

Reputation: 315

Create a String based sequence in Python SQL Alchemy

I'm trying to create a String based sequence in SQLAlchemy ORM. I know that the best practice is to use a Integer based column to manage ID of a table but in my case, due to some requirements of the project I need to store the data as a String based PK for ID's.

I know that in some databases it is possible to store data using a string based sequence like:

But in SQL alchemy I can't achieve this by using Sequence method. It only generates Integer based sequences and I can't find an options to "append" a prefix in the sequence generation.

There is a easy solution to achieve this?

Upvotes: 2

Views: 1597

Answers (3)

Kim
Kim

Reputation: 1660

It looks like the Sequence object can't help you as it's integer-based. Something like 'AAA' + str(some_numeric_id) will need to be explicitly posted to the column containing the primary key.

The following class uses itertools.product to generate a sequence of unique string ids.

import itertools


class StringCounter:
    def __init__(self, pattern: List):
        self.digit_types = {
            'uppercase': 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
            'number': '0123456789',
            'hex': '0123456789ABCDEF',
        }
        format = [self.digit_types[d] for d in pattern]
        self.sequence = (''.join(s) for s in itertools.product(*format))

    def get_n_ids(self, n):
        return [s for _, s in zip(range(n), self.sequence)]

    def next_id(self):
        return self.sequence.__next__()

    def continue_from(self, from_id):
        self.sequence = itertools.dropwhile(lambda s: s != from_id, self.sequence)

Example:

id_seq = StringCounter(['uppercase', 'uppercase', 'uppercase', 'number'])
first_batch = id_seq.get_n_ids(9)
print(f'First batch of ids: {first_batch}')
data = ['data1', 'data2', 'data3']
for d in data:
    unique_id = id_seq.next_id()
    print(f"Posting data '{d}' under unique id {unique_id}")
from_id = 'ACZ8'
id_seq.continue_from(from_id)
print(f'...continuing from {from_id}')
data = ['data4', 'data5', 'data6']
for d in data:
    unique_id = id_seq.next_id()
    print(f"Posting data '{d}' under unique id {unique_id}")

Output:

First batch of ids: ['AAA0', 'AAA1', 'AAA2', 'AAA3', 'AAA4', 'AAA5', 'AAA6', 'AAA7', 'AAA8']
Posting data 'data1' under unique id AAA9
Posting data 'data2' under unique id AAB0
Posting data 'data3' under unique id AAB1
...continuing from ACZ8
Posting data 'data4' under unique id ACZ8
Posting data 'data5' under unique id ACZ9
Posting data 'data6' under unique id ADA0

An example using two StringCounter instances together:

product_seq = StringCounter(['uppercase', 'uppercase', 'uppercase'])
sale_seq = StringCounter(['number'] * 6)
sale_seq.continue_from('000998')
for prod in range(3):
    prod_id = product_seq.next_id()
    for line in range(3):
        print(f"Posting sale '{sale_seq.next_id()}' of product id '{prod_id}'")

Output:

Posting sale '000998' of product id 'AAA'
Posting sale '000999' of product id 'AAA'
Posting sale '001000' of product id 'AAA'
Posting sale '001001' of product id 'AAB'
Posting sale '001002' of product id 'AAB'
Posting sale '001003' of product id 'AAB'
Posting sale '001004' of product id 'AAC'
Posting sale '001005' of product id 'AAC'
Posting sale '001006' of product id 'AAC'

Upvotes: 0

James
James

Reputation: 145

Not an elegant answer, but if you need to access the PK as a string, you could stay with the customary integer PK which ensures PKs are sequential and unique, then convert it to and from a string using int() / str() when accessing.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247380

You can do this in PostgreSQL similar to this:

CREATE TABLE test(
   id text PRIMARY KEY,
   val text
);

CREATE SEQUENCE test_id_seq OWNED BY test.id;

ALTER TABLE test
   ALTER id SET DEFAULT to_hex(nextval('test_id_seq'));

In that example I used the to_hex function to get the hexadecimal representation, but you can use any expression you like.

Upvotes: 2

Related Questions