CODEWITHSUNDEEP
pythonsql-serversqlalchemy
Yedidya kfir
Yedidya kfir

Reputation: 1779

Insert a row with default value column in sqlalchemy

I am writing a program which uses a database with sqlalchemy as client.

Here is one of my tables

class DownloadRecord(Base):
    __tablename__ = "DownloadRecords"
    id = Column("Id", Integer, primary_key=True, autoincrement=True)

    download_name = Column("DownloadName", Unicode, nullable=False)
    download_date = Column(
        "DownloadDate", DateTime, default=datetime.datetime.utcnow, nullable=False
    )

the column download_date is defined with a default value, both here and in the server side table. Here is the definition of the column on the mssql server

DownloadDate DATETIME NOT NULL DEFAULT GETDATE()

however I try to add the record DownloadRecord(download_name="new_download_name") and I get the following exception.

sqlalchemy.exc.IntegrityError: (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'DownloadDate', table 'DownloadRecords'; column does not allow nulls. INSERT fails. (515) (SQLExecDirectW); [23000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. (3621)") [SQL: INSERT INTO [DownloadRecords] ([DownloadName], [DownloadDate]) OUTPUT inserted.[Id] VALUES (?, ?)] [parameters: ("new_download_name", None)]

I also tried to make this column nullable but When I added the new row the DownloadDate column was Null. How do I make it automatically use the default value?

Upvotes: 0

Views: 1779

Answers (2)

Ohad Zmiry
Ohad Zmiry

Reputation: 16

The problem is incompatibility between the type of the column DownloadDate and the type of the value you give as its default value on the client side.

The type you used is DateTime (on the server side as well as the client side).

However, in the following code:

Column("DownloadDate", DateTime, default=datetime.datetime.utcnow, nullable=False)

the return value of datetime.datetime.utcnow() is a timezone aware object, while SQL Server's DateTime isn't.

I see two possible solutions:

  1. Change the default value to a callable which returns a datetime object which is not timezone aware.

  2. Change the type of the DownloadDate column to a timezone aware type. You can use SQL Server's datetimeoffset on the server side, and SQLAlchemy's DATETIMEOFFSET on the client side.

Take a look at Microsoft's docs on date and time types for the full reference.

On another note, consider moving to a code first design, where you define your schema in one place.

Upvotes: -1

Juan Ma
Juan Ma

Reputation: 34

Searching I found some people fixed by using server_default argument. But this makes the database server responsible to assign the value. 

download_date = Column(
    "DownloadDate", DateTime, server_default=text('NOW()'), nullable=False
)

Also according to SQLAlchemy documentation, ColumnDefault class is equivalent and it also may work:

download_date = Column(
    "DownloadDate", DateTime, ColumnDefault(datetime.datetime.utcnow()), nullable=False
)

But, this uses a scalar instead of a callable.

Hope it works.

Upvotes: 1

Related Questions