Generating string primary keys with sequence in SQLAlchemy
up vote
3
down vote
favorite
I need improvement on solution to the following problem:
Generating random string keys in SQA is relatively simple, something
like:
request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)
However, I need to to get
request_id
have format like
DIVISION_ABC_REQUEST_223
(this is because PK is also supposed to be
good for human consumption -- that key will be sent around in emails,
copied/pasted, etc, but it also should be usable for regular SQA/SQL
queries as a typical PK), with integer suffixes ideally following a
normal (ordinal) sequence.
(the backend DB is Postgres)
I found a solution, if a bit wasteful:
class WorkPackage(Base):
__tablename__ = 'work_package'
int_id = Column(Integer, primary_key=True)
wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
data = Column(Unicode)
@hybrid_property
def wp_id(self):
return self.wp_prefix + str(self.int_id)
@wp_id.expression
def wp_id(cls):
return cls.wp_prefix.concat(cls.int_id)
wp_id.expression
uses concat
(a ColumnOperator
that produces SQL concatenation operator ||
).
It works in the sense of automatic PK creation in desired order and for querying by the wp_id
attribute.
However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.
The obvious problem here is that wp_prefix
is a column for a single reason: so that I could use its concat
method.
I would like to change it in such way that the wp_prefix
column is unnecessary (e.g. it could be SQL concatenation of a string and int_id
column).
I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.
python sqlalchemy
add a comment |
up vote
3
down vote
favorite
I need improvement on solution to the following problem:
Generating random string keys in SQA is relatively simple, something
like:
request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)
However, I need to to get
request_id
have format like
DIVISION_ABC_REQUEST_223
(this is because PK is also supposed to be
good for human consumption -- that key will be sent around in emails,
copied/pasted, etc, but it also should be usable for regular SQA/SQL
queries as a typical PK), with integer suffixes ideally following a
normal (ordinal) sequence.
(the backend DB is Postgres)
I found a solution, if a bit wasteful:
class WorkPackage(Base):
__tablename__ = 'work_package'
int_id = Column(Integer, primary_key=True)
wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
data = Column(Unicode)
@hybrid_property
def wp_id(self):
return self.wp_prefix + str(self.int_id)
@wp_id.expression
def wp_id(cls):
return cls.wp_prefix.concat(cls.int_id)
wp_id.expression
uses concat
(a ColumnOperator
that produces SQL concatenation operator ||
).
It works in the sense of automatic PK creation in desired order and for querying by the wp_id
attribute.
However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.
The obvious problem here is that wp_prefix
is a column for a single reason: so that I could use its concat
method.
I would like to change it in such way that the wp_prefix
column is unnecessary (e.g. it could be SQL concatenation of a string and int_id
column).
I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.
python sqlalchemy
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I need improvement on solution to the following problem:
Generating random string keys in SQA is relatively simple, something
like:
request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)
However, I need to to get
request_id
have format like
DIVISION_ABC_REQUEST_223
(this is because PK is also supposed to be
good for human consumption -- that key will be sent around in emails,
copied/pasted, etc, but it also should be usable for regular SQA/SQL
queries as a typical PK), with integer suffixes ideally following a
normal (ordinal) sequence.
(the backend DB is Postgres)
I found a solution, if a bit wasteful:
class WorkPackage(Base):
__tablename__ = 'work_package'
int_id = Column(Integer, primary_key=True)
wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
data = Column(Unicode)
@hybrid_property
def wp_id(self):
return self.wp_prefix + str(self.int_id)
@wp_id.expression
def wp_id(cls):
return cls.wp_prefix.concat(cls.int_id)
wp_id.expression
uses concat
(a ColumnOperator
that produces SQL concatenation operator ||
).
It works in the sense of automatic PK creation in desired order and for querying by the wp_id
attribute.
However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.
The obvious problem here is that wp_prefix
is a column for a single reason: so that I could use its concat
method.
I would like to change it in such way that the wp_prefix
column is unnecessary (e.g. it could be SQL concatenation of a string and int_id
column).
I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.
python sqlalchemy
I need improvement on solution to the following problem:
Generating random string keys in SQA is relatively simple, something
like:
request_id = Column(String, default=lambda: uuid.uuid4().hex, primary_key=True)
However, I need to to get
request_id
have format like
DIVISION_ABC_REQUEST_223
(this is because PK is also supposed to be
good for human consumption -- that key will be sent around in emails,
copied/pasted, etc, but it also should be usable for regular SQA/SQL
queries as a typical PK), with integer suffixes ideally following a
normal (ordinal) sequence.
(the backend DB is Postgres)
I found a solution, if a bit wasteful:
class WorkPackage(Base):
__tablename__ = 'work_package'
int_id = Column(Integer, primary_key=True)
wp_prefix = Column(Unicode, default=u'DIVISION_ABC_REQUEST_', primary_key=True)
data = Column(Unicode)
@hybrid_property
def wp_id(self):
return self.wp_prefix + str(self.int_id)
@wp_id.expression
def wp_id(cls):
return cls.wp_prefix.concat(cls.int_id)
wp_id.expression
uses concat
(a ColumnOperator
that produces SQL concatenation operator ||
).
It works in the sense of automatic PK creation in desired order and for querying by the wp_id
attribute.
However, the silly aspect is that obviously there's a single column filled with the same prefix over and over.
The obvious problem here is that wp_prefix
is a column for a single reason: so that I could use its concat
method.
I would like to change it in such way that the wp_prefix
column is unnecessary (e.g. it could be SQL concatenation of a string and int_id
column).
I do not know how to construct SQLAlchemy Core expression that would achieve this without using column method.
python sqlalchemy
python sqlalchemy
asked Jan 15 '16 at 16:52
LetMeSOThat4U
3771210
3771210
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
literal_column("'DIVISION_ABC_REQUEST'").concat...
(note the double quotes, since you want an SQL string expression).
literal_column()
essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
literal_column("'DIVISION_ABC_REQUEST'").concat...
(note the double quotes, since you want an SQL string expression).
literal_column()
essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".
add a comment |
up vote
0
down vote
literal_column("'DIVISION_ABC_REQUEST'").concat...
(note the double quotes, since you want an SQL string expression).
literal_column()
essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".
add a comment |
up vote
0
down vote
up vote
0
down vote
literal_column("'DIVISION_ABC_REQUEST'").concat...
(note the double quotes, since you want an SQL string expression).
literal_column()
essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".
literal_column("'DIVISION_ABC_REQUEST'").concat...
(note the double quotes, since you want an SQL string expression).
literal_column()
essentially means "I hand you this string which is a valid SQL expression and can be used in any column expression-ish context".
answered Jun 22 at 16:07
dom0
101
101
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f116905%2fgenerating-string-primary-keys-with-sequence-in-sqlalchemy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown