在这个案例中,我们将使用Jupyter Notebook来结合SQL和Python进行数据分析
!pip install pandas
!pip install sqlite3
!pip install ipython-sql
import pandas as pd
import sqlite3
from IPython.display import display
%load_ext sql
conn = sqlite3.connect('example.db')
%sql
连接到数据库:%sql sqlite:///example.db
%%sql
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
product TEXT,
price REAL,
quantity INTEGER,
date TEXT
);
INSERT INTO sales (product, price, quantity, date) VALUES
('Product A', 10.0, 5, '2021-01-01'),
('Product B', 20.0, 10, '2021-01-01'),
('Product A', 10.0, 15, '2021-01-02'),
('Product B', 20.0, 20, '2021-01-02');
result = %sql SELECT * FROM sales;
df = result.DataFrame()
display(df)
sales_summary = df.groupby('product').sum().reset_index()
sales_summary['total_sales'] = sales_summary['price'] * sales_summary['quantity']
display(sales_summary)
conn.close()
这个简单的案例展示了如何在Jupyter Notebook中结合SQL和Python进行数据分析。你可以根据自己的需求修改代码,以处理更复杂的数据和查询。