1. Inserting data into a table
With our table created, we are ready to insert some data into the table.
2. Adding data to a table
We do this with an insert statement. Insert takes a table name as an argument, and then all the values we want to insert are added in a values clause as column=value pairs. The insert doesn't return any rows, so we don't need to use a fetch method after executing the statement. Let's insert a record into our employees table.
3. Inserting one row
After we've created the engine, established the connection, and created or reflected the table, we import the insert statement from sqlalchemy. Next, we build an insert statement for the employees table with the following values. We'll set the id column to 1, and the name column to Jason. We'll give Jason a salary of $1 and make him an active employee. With our insert statement built, we can now execute the statement and store the result proxy it returns. Remember the insert statement doesn't return any rows; however, we can use the rowcount attribute of the result proxy to see how many rows where inserted.
4. Inserting multiple rows
It is also possible to insert multiple records at one with an insert statement by building an insert statement without the values clause. Then we build a list of dictionaries to represent our column=value pairs for each record. Finally, we pass both the insert statement and the list of dictionaries to the connection's execute method which will insert all the records in the list. Let's add some more employees with a multiple insert statement.
5. Inserting multiple rows
We start by building an insert statement for the employees table without a values clause.
Next, we build a list of dictionaries, one dictionary for each record we want to insert. I typically call this list values_list to make its purpose clear. Each dictionary has the columns and associated values we want to insert. We pass both the insert statement and the list of dictionaries to the execute method on connection. Again there is no need to call a fetch method since an insert returns no rows. Finally, we use the rowcount attribute of the result proxy to check how many records were inserted.
6. Let's practice!
Now it's time for you to try your hand at inserting data.