In the preceding bankruptcy, we have learnt SQL Expressions. In this bankruptcy, we will look at the execution of these expressions.
In order to execute the resulting SQL expressions, we must gain a connection item representing an actively checked out DBAPI connection useful resource after which feed the expression item as proven in the code below.
conn = engine.connect()
The following insert() object can be used for execute() method −
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)
The console indicates the result of execution of SQL expression as beneath −
INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMIT
Following is the entire snippet that shows the execution of INSERT question the usage of SQLAlchemy’s middle approach −
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
ins = students.insert()
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
conn = engine.connect()
result = conn.execute(ins)
The end result may be validated through commencing the database the use of SQLite Studio as shown in the beneath screenshot −
The result variable is called a ResultProxy item. It is similar to the DBAPI cursor item. We can gather statistics approximately the number one key values which have been generated from our assertion the use of ResultProxy.Inserted_primary_key as shown under −
result.inserted_primary_key
[1]
To difficulty many inserts the usage of DBAPI’s execute many() approach, we can ship in a list of dictionaries each containing a awesome set of parameters to be inserted.
conn.execute(students.insert(), [
{'name':'Rajiv', 'lastname' : 'Khanna'},
{'name':'Komal','lastname' : 'Bhandari'},
{'name':'Abdul','lastname' : 'Sattar'},
{'name':'Priya','lastname' : 'Rajhans'},
])
This is meditated inside the statistics view of the desk as proven inside the following figure −