Learn

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

1.

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.

2.

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.

Sign up to start coding

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?