Working With SQL
00:00 Working with SQL files. pandas IO Tools can also read and write databases. In this next example, you’ll write your data to a database, but to get started, you’ll need to install the SQLAlchemy package, as seen onscreen now.
In addition, you’ll also need a database driver. Fortunately, Python has a built-in driver for SQLite databases, so that’s what we’ll be using. Once you have SQLAlchemy installed, import
create_engine() and create a database engine as seen onscreen.
.astype() is a convenient method you can use to set multiple data types all at once using a dictionary as seen onscreen. Now the DataFrame can be saved to a database using the
.to_sql() method as seen onscreen.
The first column contains the row labels. The other columns correspond to the columns of the DataFrame. To omit writing row labels into the database, pass
index=False to the
.to_sql() method, and this will lead to the database seen onscreen now. There are a few more optional parameters, for example, you can use
schema to specify the database schema and
dtype to determine the types of the database columns. You can also use
if_exists, which says what to do if a database with the same name and path already exists.
Now you have the same
DataFrame object has before. Note that the content for Russia is now
None instead of
nan. If you want to fill in the missing values with
nan, then you can use
.fillna() as seen onscreen.
Also note that you didn’t have to pass
read_sql(). That’s because the database was able to detect that the last column contains dates. However, you could pass
parse_dates if you’d like, but you’d still get the same results.
Become a Member to join the conversation.