Nice work! Now that we have connected to the titanic.db database, we can execute SQL commands using the cursor object. To start a SQL command (also known as a SQL statement), we must attach the .execute()
method to your cursor object as such, cursor.execute()
.
Create a Table
Let’s go over a few simple SQL commands. The SQL command CREATE TABLE
creates a new table in the connected database. Look at this example below:
# Create cursor object curs = connection.cursor() # Create table named toys curs.execute('''CREATE TABLE toys ( id INTEGER, name TEXT, price REAL, type TEXT)''')
In the code above, we use the .execute()
method with the curs
object to create a table named toys
. Inside the parentheses is a list. The first word in each item is the name of the field and the second word identifies the type of the field. The first field in the toys
table is called id
; its data type is INTEGER
. To understand more about these data types, you may click here.
Additionally, we used a triple-quoted string because the SQL command string stretched across multiple lines. We recommend always using a triple-quote ('''
or """
) around the SQL statement to avoid any possible errors.
Insert Data
After creating a data table, we may want to insert the data. To do this, we will use the SQL command INSERT INTO table_name VALUES
. Here is an example of this:
# Insert a row of data in the toys table curs.execute('''INSERT INTO toys VALUES (2244560, 'Ultimate Ninja Fighter', 24.99, 'action')''')
This line of code added a row of data, (2244560, 'Ultimate Ninja Fighter', 24.99, 'action')
into the toys
table. Notice That the list is set up in the same order that we created the fields.
Instructions
The module sqlite3
, the connection to the titanic.db
database, and the cursor object are in the code editor to your right.
Create a table using the CREATE TABLE
SQL command. Name this table new_table
and include the fields name
, age
, username
, and pay_rate
. The field types are TEXT
, INTEGER
, TEXT
, and REAL
, respectively.
Using the SQL commands INSERT INTO table_name VALUES
, insert this row, ('Bob Peterson', 34, 'bob1234', 40.00)
into the table you just created, new_table
.