Autoincrement ID Support in SQLAlchemy

How to set primary key auto increment in sqlalchemy

Introduction

When working with a database, it is often necessary to create tables with unique identifiers for each row. One way to do this is by using autoincrement IDs. SQLAlchemy, a popular Python SQL toolkit, provides built-in support for autoincrement IDs. In this blog post, we’ll explore how to use autoincrement IDs in SQLAlchemy schema definitions and inserts.

Defining a Table with an Autoincrement ID

To define a table with an autoincrement ID in SQLAlchemy, we can use the Column class with the primary_key and autoincrement arguments. Here's an example:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)

In this example, we define a User table with an id column that is an Integer type, is the primary_key, and has autoincrement enabled. We also define a name column that is a String type.

Inserting Data with an Autoincrement ID

To insert data into a table with an autoincrement ID, we don’t need to specify a value for the id column. SQLAlchemy will automatically generate an ID for us. Here's an example:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


engine = create_engine('sqlite:///example.db')
Session = sessionmaker(bind=engine)
session = Session()

user = User(name='John Doe')
session.add(user)
session.commit()

print(user.id) # Output: 1

In this example, we first create an engine object that connects to a SQLite database file. We then create a Session object from the sessionmaker function and use it to create a new User object with a name of 'John Doe'. We add the user to the session and commit the changes. Finally, we print the id of the user, which is automatically generated by SQLAlchemy and is equal to 1.

Conclusion

Autoincrement IDs are a convenient way to create unique identifiers for each row in a database table. In SQLAlchemy, we can easily define a table with an autoincrement ID by using the Column class with the primary_key and autoincrement arguments. When inserting data into a table with an autoincrement ID, we don't need to specify a value for the id column. SQLAlchemy will automatically generate an ID for us. With these tools, we can easily create robust and scalable database applications in Python.