Sqlalchemy core, insert multiple rows from a tuple instead of dict

SQLAlchemy is a popular Python library used for working with databases. SQLAlchemy provides an Object-Relational Mapping (ORM) layer and a Core layer. The ORM layer allows developers to work with databases using Python objects, while the Core layer provides a lower-level interface for SQL-oriented database work. In this article, we’ll explore how to use SQLAlchemy Core to insert multiple rows into a database table from a tuple instead of using a dictionary.

Inserting multiple rows into a database table is a common operation in database applications. The traditional way to do this is to use a dictionary to define the values ​​for each row and pass that dictionary to the insert statement. However, in some cases, it may be more appropriate to use a tuple to define the values ​​for each row.

Creating a table :

To demonstrate inserting multiple rows into a database table from a tuple, we first need to create a table. In this example, we will create a table called ‘orr’ with columns ‘name‘, ‘age’ and ‘salary‘.

Python

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, select
engine = create_engine('postgresql://postgres:root@localhost:5432/demo')

metadata = MetaData()

orr = Table('orr', metadata,
            Column('name', String),
            Column('age', Integer),
            Column('salary', Integer)
            )

metadata.create_all(engine)

In this code, we create a SQLite database engine and define a metadata object. Then we define a table called ‘orr’ with columns  ‘name’, ‘age’ and ‘salary’. Finally, we create the table using the ‘create_all‘ method.

Inserting multiple lines :

To insert multiple rows into the ‘orr’ table from a tuple, we can use the ‘insert’ command with the ‘values’ method. The ‘values’ method takes a list of dictionaries, where each dictionary represents a row to be inserted. However, we can also use a list of tuples where each tuple represents a row to be inserted.

Python

from sqlalchemy import insert

conn = engine.connect()

rows = [
    ('Alice', 25, 50000),
    ('Bob', 30, 60000),
    ('Charlie', 35, 70000)
]

stmt = insert(orr).values(
    [{'name': name, 'age': age, 'salary': salary} for name, age, salary in rows])
# conn.execute(stmt)
with engine.begin() as conn:
    conn.execute(stmt)

In this code, we create a list of tuples called ‘rows’ where each tuple represents a row to be inserted. We then create an insert statement using the ‘insert’ function and pass the ‘orr’ table object. We use a list of dictionaries to create a list of dictionaries, where each dictionary represents a row to be inserted. Finally, we execute the statement using the ‘execute’ method on the database connection object.

Output:

Output

Conclusion :

In this article, we explored how to use SQLAlchemy Core to insert multiple rows into a database table from a tuple instead of using a dictionary. We created a table called ‘orr’ with columns ‘name’, ‘age’ and ‘salary’ and then used the ‘insert’ statement with the ‘values’ method to insert multiple rows into the table from a list of tuples. This approach can be useful in cases where it is more appropriate to define row values ​​as tuples rather than dictionaries.