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( 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( 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,
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.