Manipulating SQL data

Earlier, when we introduced SQL, we showed you a simple example where we took our existing bike_share data, created a database, and stored the data as a table in the new database. We mentioned that you can execute SQL commands by using the cursor.execute() method. Here, you will see another example, this time using a database that contains multiple tables.

Suppose you have a database for a pet supply company. You can use pd.read_sql() to issue a command to the database and return the names of all the tables. SQL databases can contain multiple tables, so there is a "master" table, called sqlite_master here, that can be queried so that you can see every other table in the database. In the following snippet, the SELECT statement says to get a variable called name and return its values where the variable called type is 'table' – in other words, a list of tables. The sqlite3.connect() statement opens a connection to the database so that...