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,
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 (
""") around the SQL statement to avoid any possible errors.
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.
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
pay_rate. The field types are
Using the SQL commands
INSERT INTO table_name VALUES, insert this row,
('Bob Peterson', 34, 'bob1234', 40.00) into the table you just created,