Create a String based sequence in Python SQL Alchemy

Question

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:

"AAA1"
"AAA2"
"AAAN"

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?

Laurenz Albe · Accepted Answer · 2017-04-03 08:09:55Z

2

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.

answered Apr 3, 2017 at 8:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James · Accepted Answer · 2020-06-25 12:44:04Z

0

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.

answered Jun 25, 2020 at 12:44

James

1455 bronze badges

Comments

Kim · Accepted Answer · 2020-07-07 00:15:24Z

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'

Collectives™ on Stack Overflow

Create a String based sequence in Python SQL Alchemy

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related