【PYTHON】collect the data using a SQL query

engine = create_engine('sqlite:///chinook.db')

query = """SELECT SUM(Total) as Total , strftime('%m-%Y', InvoiceDate) as 'month-year', BillingCountry

FROM invoices

WHERE BillingCountry="USA"

GROUP BY strftime("%m-%Y", InvoiceDate);

"""

with engine.connect() as connection:

  sql_df2 = pd.read_sql_query(query, connection)

sql_df2.head()

# convert to datetime for better plotting

sql_df2['month-year'] = pd.to_datetime(sql_df2['month-year'])

sns.lineplot(data=sql_df2, x='month-year', y='Total', hue='BillingCountry')

plt.xlabel('Year')