1. Creating a database engine in Python
All right, we're back! What we really want to do is to get data out of our databases using SQL, or the Structured Query Language. But before we get to that, we're going to need to figure out how to connect to a database.
2. Creating a database engine
We'll use an SQLite database as an example because SQLite is fast and simple while still containing enough functionality to introduce you to all the necessary concepts of querying a database. There are times when you would prefer to use PostgreSQL or MySQL, but for our purposes here, an Introduction to Interacting with Relational Databases in Python, a SQLite database is perfect. We'll once again look at the Northwind database.
There are many packages we could use to access an SQLite database such as sqlite3 and SQLAlchemy. We'll use SQLAlchemy as it works with many other Relational Database Management Systems, such as Postgres and MySQL.
So without further ado, to connect to 'Northwind dot sqlite', we need to import the relevant funtion create_engine from the package SQLAlchemy.
We then use the function create_engine to fire up an SQL engine that will communicate our queries to the database. The only required argument of create_engine is a string that indicates the type of database you're connecting to and the name of the database.
Next, In order to query the database, we need to connect to the engine to do so.
3. Getting table names
But before we do this, we would like to know the names of the tables it contains.
To do this, apply the method table_names to the object engine. This will return a list of the table names that you can then print the console. Now it's your turn to do the same: fire up the database engine and print the table names!
4. Let's practice!
After this, I'll be back to show you how to connect to the engine, query your DBs and then you'll get loads of practice writing your own queries to import data from relational databases!