Pandas with SQL databases - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to read a SQL table into a pandas DataFrame.

Pandas

import pandas as pd
import sqlite3
conn = sqlite3.connect('example.db')
df = pd.read_sql_table([1], conn)

Drag options to blanks, or click blank then click option'

Aconn

B'users'

C'SELECT * FROM users'

Dusers

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the table name without quotes.

Passing a SQL query instead of a table name.

The read_sql_table function requires the table name as a string.

2fill in blank

medium

Complete the code to execute a SQL query and load the result into a DataFrame.

Pandas

query = 'SELECT * FROM sales WHERE amount > 100'
df = pd.read_sql_query([1], conn)

Drag options to blanks, or click blank then click option'

A'query'

Bconn

Cquery

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the connection object instead of the query string.

Passing the query string with extra quotes.

The read_sql_query function expects the SQL query string as the first argument.

3fill in blank

hard

Fix the error in the code to write a DataFrame to a SQL table.

Pandas

df.to_sql([1], conn, if_exists='replace', index=False)

Drag options to blanks, or click blank then click option'

Atable_name

Bconn

Cdf

D'table_name'

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the connection object as the first argument.

Passing the DataFrame itself as the first argument.

The first argument to to_sql must be the table name as a string.

4fill in blank

hard

Fill both blanks to create a DataFrame from a SQL query and set the index column.

Pandas

df = pd.read_sql_query([1], conn, [2]='id')

Drag options to blanks, or click blank then click option'

A'SELECT * FROM employees'

Bindex_col

C'id'

D'conn'

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the connection as the first argument.

Using the wrong keyword for setting the index column.

The first argument is the SQL query string, and index_col sets the DataFrame index column.

5fill in blank

hard

Fill all three blanks to write a DataFrame to a SQL table with append mode and without the index.

Pandas

df.to_sql([1], conn, if_exists=[2], index=[3])

Drag options to blanks, or click blank then click option'

A'logs'

B'append'

CFalse

DTrue

Attempts:

3 left

💡 Hint

Common Mistakes

Using if_exists='replace' which deletes existing data.

Setting index=True which writes the index as a column.

Use the table name as a string, if_exists='append' to add data, and index=False to exclude the index.