Using Sqlalchemy to insert MySQL Timestamp Column Values

This article is about how we can add a timestamp column with values in MYSQL along with other data in an SQL database. Timestamp is quite useful as it provides the time when that particular entity was created in the database. Here we will use SQLAlchemy as it is a popular Python programming SQL toolkit and ORM (Object Relational Mapper) that gives software developers the power and flexibility of querying SQL databases using Python.

Required Modules:

MySQL installed if not you can refer to this article, Once installed we will configure it to work along with Python as follow:

pip install SQLAlchemy
pip install psycopg2

Configure MySQL Database with Python

Now we need a database that we will be using for the demonstration, which can be created and configured by running the following commands in SQL shell.

Connecting MYSQL Using Python

Here we are using Sqlalchemy ‘create_engine’ to connect to the MySQL database.

Python3

# Import necessary libraries
import datetime
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, Column, Integer, String, TIMESTAMP

# Create engine and sessionmaker
engine = create_engine(
    'mysql+mysqlconnector://root:1234@localhost:3306/gfg',
                                          echo=False)

# session instance is used to communicate with the database
Session = sessionmaker(bind=engine)
session = Session()

Creating a Table for Demonstration WIth TimeStamp

Now that we have the MySQL database connected with Python we now create a table name ‘users’ using SQLAlchemy for this demonstration using the following Python script.

Python3

# Import necessary libraries
import datetime
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, Column, Integer, String, TIMESTAMP

# Create engine and sessionmaker
engine = create_engine(
    'mysql+mysqlconnector://root:1234@localhost:3306/gfg',
                                          echo=False)

# session instance is used to communicate with the database
Session = sessionmaker(bind=engine)
session = Session()

# Create base class
Base = declarative_base()

# Define table class
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    created_at = Column(TIMESTAMP,
                        default=datetime.datetime.utcnow)


# Create table
Base.metadata.create_all(engine)

Output:

It has created a table name ‘users’ in the MySQL database with the following columns as shown below,

Output before running

Inserting Data into the Table 

To insert data we need some data according to table constraints, here we use Python to insert a single entry with the current time retrieved using Python ‘datetime’ module into the MySQL database. By running the below code we have inserted a single entry with the timestamp of the creation time.

Python3

# Insert values

# Create user objects
user1 = User(name='John')
user2 = User(name='Smith')
user3 = User(name='Peter')

# Add user objects to session
session.add(user1)
session.add(user2)
session.add(user3)

# Commit the changes to the database
session.commit()

Output:

As you can see there are 3 entries with respective timestamps in the MYSQL database.

Output