current position:Home>Python meets SQL, so a useful Python third-party library appears

Python meets SQL, so a useful Python third-party library appears

2022-05-15 04:48:49Game programming

1. Presentation data

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 .

Python run into sb. SQL, So a useful Python The third-party library appears - The first 1 Zhang

2. pandasql Use

1) brief introduction

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 pdfrom pandasql import sqldf

2) To declare a global variable 2 Ways of planting

  • ① Before use , Declare the global variable ;

  • ② Declare global variables at one time ;

① Before use , Declare the global variable
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 df1global df2global df3global df4query1 = "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 :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 2 Zhang
② 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")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 :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 3 Zhang

3) Write a few simple SQL sentence

① see sqlite Version of
student = pd.read_excel("student.xlsx")pysqldf = lambda q: sqldf(q, globals())query1 = """    select sqlite_version(*)"""pysqldf(query1)

give the result as follows :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 4 Zhang
② where Screening
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 :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 5 Zhang
③ Multiple table joins
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 :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 6 Zhang
④ Group aggregation
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 :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 7 Zhang
⑤ union Inquire about
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 :

Python run into sb. SQL, So a useful Python The third-party library appears - The first 8 Zhang

author : The beauty of data analysis and statistics

Game programming , A game development favorite ~

If the picture is not displayed for a long time , Please use Chrome Kernel browser .

copyright notice
author[Game programming],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/135/202205142211295496.html

Random recommended