Let’s insert some data, following from the previous post. This time we’ll create emp4.db.

import sqlalchemy as sa
engine = sa.create_engine(r'sqlite:///D:\Test\emp4.db') 
connection = engine.connect()

meta_data = sa.MetaData()

employees = sa.Table(
    'Employees', meta_data,
    sa.Column('id', sa.Integer(), primary_key=True),
    sa.Column('name', sa.String()),
    sa.Column('position', sa.String())
)

meta_data.create_all(engine)
query = employees.insert().values(name='Tracy',position='python programmer')
# you don't have to include all column names (null if you don't)
result = connection.execute(query)
# just to see what it looks like we'll print it
print(result)
# <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x000001EF1E516AD0>

If we were to run this again, but change the name to Tracy and the position to ‘python programmer’, this is what we would see in DB Browser for SQLite when we browsed the table Employees in emp3.db.

What if we wanted to insert several rows at a time?

Inserting Many Values

If you want to insert many values into the database, you can use a list of dictionaries.

# a list of dictionaries
employee_list = [
    {'name':'bart', 'position':'vp operations'},
    {'name':'hugh', 'position':'janitorial engineering'},
    {'name':'suzie', 'position':'manager'}
]
result = connection.execute(employees.insert(), employee_list)

Here is the screenshot of the DB Browser. Note Tracy was added twice, which is okay for our example here.