All the demonstration data of this article , Are based on the following four tables . The following four tables should be familiar to you , This is it. Net transmission 50 Taoist classics MySQL Interview questions Several original tables used in . About the relationship between the following tables , I won't explain it to you , Look closely at the field name , Should be able to find . Like this article, remember to collect 、 Focus on 、 give the thumbs-up .
notes ： Technical communication 、 Data acquisition , See you at the end of the article
pandas Medium DataFrame It's a two-dimensional table , The table in the database is also a two-dimensional table , So in pandas Use in sql sentence It seems natural ,pandasql Use SQLite As its operational database , meanwhile Python Bring their own SQLite modular , No installation required , It can be used directly .
One thing to note here is that ： Use pandasql Read DataFrame Columns in date format , By default, the date will be read 、 Minutes and seconds , So we should learn to use sqlite Date handling functions in , It is convenient for us to convert the date format , Below is sqlite A complete collection of commonly used functions in , I hope it helps you .
sqlite Function Daquan ：http://suo.im/5DWraE
Import related libraries ：
import pandas as pd
from pandasql import sqldf
① Before use , Declare the global variable ;
② Declare global variables at one time ;
df1 = pd.read_excel("student.xlsx")
df2 = pd.read_excel("sc.xlsx")
df3 = pd.read_excel("course.xlsx")
df4 = pd.read_excel("teacher.xlsx")
global df1
global df2
global df3
global df4
query1 = "select * from df1 limit 5"
query2 = "select * from df2 limit 5"
query3 = "select * from df3"
query4 = "select * from df4"
sqldf(query1)
sqldf(query2)
sqldf(query3)
sqldf(query4)
Some of the results are as follows ：
df1 = pd.read_excel("student.xlsx")
df2 = pd.read_excel("sc.xlsx")
df3 = pd.read_excel("course.xlsx")
df4 = pd.read_excel("teacher.xlsx")
pysqldf = lambda q: sqldf(q, globals())
query1 = "select * from df1 limit 5"
query2 = "select * from df2 limit 5"
query3 = "select * from df3"
query4 = "select * from df4"
sqldf(query1)
sqldf(query2)
sqldf(query3)
sqldf(query4)
Some of the results are as follows ：
student = pd.read_excel("student.xlsx")
pysqldf = lambda q: sqldf(q, globals())
query1 = """ select sqlite_version(*) """
pysqldf(query1)
give the result as follows ：
student = pd.read_excel("student.xlsx")
pysqldf = lambda q: sqldf(q, globals())
query1 = """ select * from student where strftime('%Y-%m-%d',sage) = '1990-01-01' """
pysqldf(query1)
give the result as follows ：
student = pd.read_excel("student.xlsx")
sc = pd.read_excel("sc.xlsx")
pysqldf = lambda q: sqldf(q, globals())
query2 = """ select * from student s join sc on s.sid = sc.sid """
pysqldf(query2)
Some of the results are as follows ：
student = pd.read_excel("student.xlsx")
sc = pd.read_excel("sc.xlsx")
pysqldf = lambda q: sqldf(q, globals())
query2 = """ select s.sname as full name ,sum(sc.score) as Total score from student s join sc on s.sid = sc.sid group by s.sname """
pysqldf(query2)
give the result as follows ：
student = pd.read_excel("student.xlsx")
pysqldf = lambda q: sqldf(q, globals())
query1 = """ select * from student where strftime('%Y-%m',sage) = '1990-01' union select * from student where strftime('%Y-%m',sage) = '1990-12' """
pysqldf(query1)
give the result as follows ：
At present, a technical exchange group has been opened , Group friends have exceeded 3000 people , The best way to add notes is ： source + Interest direction , Easy to find like-minded friends
The way ①、 Send the following picture to wechat , Long press recognition , The background to reply ： Add group ;
The way ②、 Add microsignals ：dkl88191, remarks ： come from CSDN
The way ③、 WeChat search official account ：Python Learning and data mining , The background to reply ： Add group 
