Sqlalchemy: how to define constraint newid() for Guid column (mssql)

user_9751966 0
Viewed 653

I want to create a column (Id) of type uniqueidentifier in sqlalchemy in a table called Staging.Transactions. Also, I want the column to automatically generate new guids for inserts.

What I want to accomplish is the following (expressed in sql)

ALTER TABLE [Staging].[Transactions] ADD  DEFAULT (newid()) FOR [Id]
GO

The code in sqlalchemy is currently:

from sqlalchemy import Column, Float, Date
import uuid
from database.base import base
from sqlalchemy_utils import UUIDType


class Transactions(base):
    __tablename__ = 'Transactions'
    __table_args__ = {'schema': 'Staging'}

    Id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
    AccountId = Column(UUIDType)
    TransactionAmount = Column(Float)
    TransactionDate = Column(Date)

    def __init__(self, account_id, transaction_amount, transaction_date):
        self.Id = uuid.uuid4()
        self.AccountId = account_id
        self.TransactionAmount = transaction_amount
        self.TransactionDate = transaction_date

When I create the schema from the python code it does not generate the constraint that I want in SQL - that is - to auto generate new guids/uniqueidentifiers for the column [Id].

enter image description here

If I try to make a manual insert I get error message: "Cannot insert the value NULL into column 'Id', table 'my_database.Staging.Transactions'; column does not allow nulls. INSERT fails."

Would appreciate tips on how I can change the python/sqlalchemy code to fix this.

sqlalchemy sql-server python
1 Answers

I've found two ways:

1)

Do not use uuid.uuid4() in init of your table class, keep it simple:

class Transactions(base):
    __tablename__ = 'Transactions'
    __table_args__ = {'schema': 'Staging'}

    Id = Column(String, primary_key=True)
    ...

    def __init__(self, Id, account_id, transaction_amount, transaction_date):
        self.Id = Id
        ...

Instead use it in the creation of a new record:

import uuid

...

new_transac = Transacatins(Id = uuid.uuid4(),
              ...
              )

db.session.add(new_transac)
db.session.commit()

Here, db is my SQLAlchemy(app)

2)

Without uuid, you can use raw SQL to do de job (see SQLAlchemy : how can I execute a raw INSERT sql query in a Postgres database?).

Well... session.execute is a Sqlalchemy solution...

In your case, should be something like this:

table = "[Staging].[Transactions]"
columns = ["[Id]", "[AccountId]", "[TransactionAmount]", "[TransactionDate]"]
values = ["(NEWID(), \'"+str(form.AccountId.data) +"\', "+\
                         str(form.TransactionAmount.data) +", "+\
                         str(form.TransactionDate.data))"]
s_cols = ', '.join(columns)
s_vals = ', '.join(values)

insSQL = db.session.execute(f"INSERT INTO {table} ({s_cols}) VALUES {s_vals}")

print (insSQL) # to see if SQL command is OK          

db.session.commit()

You have to check where single quotes are really needed.

user_109420250
Related