Connecting to a MySQL database
Before you jump into the calculation exercises, let's begin by connecting to our database. Recall that in the last chapter you connected to a PostgreSQL database. Now, you'll connect to a MySQL database, for which many prefer to use the pymysql database driver, which, like psycopg2 for PostgreSQL, you have to install prior to use.
This connection string is going to start with 'mysql+pymysql://', indicating which dialect
and driver you're using to establish the connection. The dialect block is followed by the 'username:password' combo.
Next, you specify the host and port with the following '@host:port/'. Finally, you wrap up the
connection string with the 'database_name'.
Now you'll practice connecting to a MySQL database: it will be the same census database that you have already been working with. One of the great things about SQLAlchemy is that, after connecting, it abstracts over the type of database it has connected to and you can write the same SQLAlchemy code, regardless!
This is a part of the course
“Introduction to Databases in Python”
Exercise instructions
- Import the
create_enginefunction from thesqlalchemylibrary. - Create an engine to the
censusdatabase by concatenating the following strings and passing them tocreate_engine():'mysql+pymysql://'(the dialect and driver).'student:datacamp'(the username and password).'@courses.csrrinzqubik.us-east-1.rds.amazonaws.com:3306/'(the host and port).'census'(the database name).
- Use the
.table_names()method onengineto print the table names.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Import create_engine function
from sqlalchemy import create_engine
# Create an engine to the census database
engine = create_engine(____)
# Print the table names
print(____)
This exercise is part of the course
Introduction to Databases in Python
In this course, you'll learn the basics of relational databases and how to interact with them.
In this chapter, you will learn to perform advanced—and incredibly useful—queries that enable you to interact with your data in powerful ways.
Exercise 1: Calculating values in a queryExercise 2: Connecting to a MySQL databaseExercise 3: Calculating a difference between two columnsExercise 4: Determining the overall percentage of womenExercise 5: SQL relationshipsExercise 6: Automatic joins with an established relationshipExercise 7: JoinsExercise 8: More practice with joinsExercise 9: Working with hierarchical tablesExercise 10: Using alias to handle same table joined queriesExercise 11: Leveraging functions and group_bys with hierarchical dataExercise 12: Handling large ResultSetsExercise 13: Working on blocks of recordsWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.