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