SQLAlchemy/Pandas: Can not insert default time in MySQL

Question

I create a table using sqlalchemy, and I want to set a default timestamp in mysql, but it can not insert into mysql, my code:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float, ForeignKey,TIMESTAMP

Base = declarative_base()
DBSession = sessionmaker(bind=engine)
session = DBSession()

class Book(Base):
    __tablename__ = 'book'
    bid = Column(Integer, primary_key=True)
    bname = Column(String(20))
    price = Column(Integer)

    student_id = Column(Integer, ForeignKey(Student.id))

    student = relationship(Student)
    insert_time = Column(TIMESTAMP(timezone=False),
                         default=datetime.datetime.now())

Base.metadata.create_all(engine)
books = pd.DataFrame({"bname": ['a','b','c','d'],
                      "price":[128,22,67,190],
                      'student_id':[1,1,3,2]})
books.to_sql('book',engine,if_exists='append',index=False)

look at the book table, the insert_time field is null, not the datetime, how I deal with it?

Ilja Everilä · Accepted Answer · 2018-09-14 11:16:00Z

When using SQLAlchemy you should strive to understand what takes place in Python and what in the database. Here you have defined a default value for insert_time using the default argument, compared to using server_default, so the default value is added to the values in Python, if no value for the column was specified, and only if you are using either the specified model or underlying Table.

If you created the table based on the model you have shown, then there is no server side default value and the column is nullable. This matters, because Pandas does a bulk insert using its own metadata, so it does not know about your Python side default.

In MySQL – depending on version and flags – a column of type TIMESTAMP is implicitly NOT NULL unlike any other type and the first timestamp column has a server side default of CURRENT_TIMESTAMP, but SQLAlchemy emits the NULL specifier explicitly if nullable=False is not specified, in order to make the behaviour consistent with other DBMS.

Finally, you most probably meant to pass the function datetime.datetime.now as the default, not an instance of datetime that you get during class construction when you call the function:

    insert_time = Column(TIMESTAMP(timezone=False),
                         default=datetime.datetime.now)  # Don't call it, pass it

You should also explicitly specify a server side default, if using Pandas as you have shown:

    # Having `default` as well is a bit redundant.
    insert_time = Column(TIMESTAMP(timezone=False),
                         default=datetime.datetime.now,
                         server_default=text('CURRENT_TIMESTAMP'),
                         nullable=False)

Manish Maurya · Accepted Answer · 2018-09-14 13:23:07Z

0

You can use Datetime instance of TIMESTAMP

import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float, ForeignKey,DateTime

Base = declarative_base()
DBSession = sessionmaker(bind=engine)
session = DBSession()

class Book(Base):
    __tablename__ = 'book'
    bid = Column(Integer, primary_key=True)
    bname = Column(String(20))
    price = Column(Integer)

    student_id = Column(Integer, ForeignKey(Student.id))

    student = relationship(Student)
    insert_time = Column(Datetime,
                         default=datetime.datetime.now())

Base.metadata.create_all(engine)
books = pd.DataFrame({"bname": ['a','b','c','d'],
                      "price":[128,22,67,190],
                      'student_id':[1,1,3,2]})
books.to_sql('book',engine,if_exists='append',index=False)

edited Sep 14, 2018 at 13:23

answered Sep 14, 2018 at 10:21

Manish Maurya

494 bronze badges

1 Comment

Ilja Everilä Over a year ago

The column's still nullable though, and the (static) default is not used in pandas.

Collectives™ on Stack Overflow

SQLAlchemy/Pandas: Can not insert default time in MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related