Skip to content

Python

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

Python

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
00 SELECT basics.ipynb
00 SELECT basics.ipynb
 
 
01 SELECT name.ipynb
01 SELECT name.ipynb
 
 
02 SELECT from World.ipynb
02 SELECT from World.ipynb
 
 
03 SELECT from Nobel.ipynb
03 SELECT from Nobel.ipynb
 
 
04 SELECT within SELECT.ipynb
04 SELECT within SELECT.ipynb
 
 
05 SUM and COUNT.ipynb
05 SUM and COUNT.ipynb
 
 
06 JOIN.ipynb
06 JOIN.ipynb
 
 
07 More JOIN operations.ipynb
07 More JOIN operations.ipynb
 
 
08 Using Null.ipynb
08 Using Null.ipynb
 
 
08+ Numeric Examples.ipynb
08+ Numeric Examples.ipynb
 
 
09 Self join.ipynb
09 Self join.ipynb
 
 
09+ COVID 19.ipynb
09+ COVID 19.ipynb
 
 
09- Window function.ipynb
09- Window function.ipynb
 
 
11 Module Feedback.ipynb
11 Module Feedback.ipynb
 
 
12-1 Helpdesk - Easy.ipynb
12-1 Helpdesk - Easy.ipynb
 
 
12-2 Helpdesk - Medium.ipynb
12-2 Helpdesk - Medium.ipynb
 
 
12-3 Helpdesk - Hard.ipynb
12-3 Helpdesk - Hard.ipynb
 
 
13-1 Guest House - Easy.ipynb
13-1 Guest House - Easy.ipynb
 
 
13-2 Guest House - Medium.ipynb
13-2 Guest House - Medium.ipynb
 
 
13-3 Guest House - Hard.ipynb
13-3 Guest House - Hard.ipynb
 
 
14-1 AdventureWorks - Easy.ipynb
14-1 AdventureWorks - Easy.ipynb
 
 
14-2 AdventureWorks - Medium.ipynb
14-2 AdventureWorks - Medium.ipynb
 
 
14-3 AdventureWorks - Hard.ipynb
14-3 AdventureWorks - Hard.ipynb
 
 
14-4 AdventureWorks - Resit.ipynb
14-4 AdventureWorks - Resit.ipynb
 
 
15-1 University Timetables - Easy.ipynb
15-1 University Timetables - Easy.ipynb
 
 
15-2 University Timetables - Medium.ipynb
15-2 University Timetables - Medium.ipynb
 
 
15-3 University Timetables - Hard.ipynb
15-3 University Timetables - Hard.ipynb
 
 
15-4 University Timetables - Resit.ipynb
15-4 University Timetables - Resit.ipynb
 
 
16-1 Musicians - Easy.ipynb
16-1 Musicians - Easy.ipynb
 
 
16-2 Musicians - Medium.ipynb
16-2 Musicians - Medium.ipynb
 
 
16-3 Musicians - Hard.ipynb
16-3 Musicians - Hard.ipynb
 
 
17-0 Dressmaker - Samples.ipynb
17-0 Dressmaker - Samples.ipynb
 
 
17-1 Dressmaker - Easy.ipynb
17-1 Dressmaker - Easy.ipynb
 
 
17-2 Dressmaker - Medium.ipynb
17-2 Dressmaker - Medium.ipynb
 
 
17-3 Dressmaker - Hard.ipynb
17-3 Dressmaker - Hard.ipynb
 
 
18-1 Congestion - Easy.ipynb
18-1 Congestion - Easy.ipynb
 
 
18-2 Congestion - Medium.ipynb
18-2 Congestion - Medium.ipynb
 
 
18-3 Congestion - Hard.ipynb
18-3 Congestion - Hard.ipynb
 
 
19 White Christmas.ipynb
19 White Christmas.ipynb
 
 
README.md
README.md
 
 

README.md

SQLZOO试题的Python (Pandas) 实现 | Python (pandas) Implementation of SQLZOO Solutions

步骤 Steps

  1. 需要用到pandas、SQLAlchemy包。此外，本实验采用PostgreSQL，需要psycopg2包。在Anaconda环境下安装即可：

    conda install pandas sqlalchemy psycopg2

  2. 启动数据库localhost/sqlzoo。

  3. 通过sqlalchemy形式的DB URI连接sqlzoo数据库。

  1. pandas, SQLAlchemy are required. Also, PostgreSQL is applied in this case, so psycopg2 is also required. You can use conda/pip to install them.

    conda install pandas sqlalchemy psycopg2

  2. Boot the database localhost/sqlzoo

  3. Connect to the sqlzoo database by sqlalchemy compatible DB URI

macOS Mojave不能安装mysql-python，只能装pymysql。因此，如果用MySQL的话，URI要写成mysql+pymysq://...。 macOS Majave cannot install mysql-python currently. Install pymysql insstead and write the URI in the form of mysql+pymysq://....

import getpass
import psycopg2
from sqlalchemy import create_engine
import pandas as pd
pwd = getpass.getpass()
engine = create_engine(
    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))

可以针对pandas做一些设置，比如将长度显示行数的上限为10。 Do some configuration after connection, e.g., set the display upper limit of rows = 10.

pd.set_option('display.max_rows', 10)

举例 Example

pd.read_sql_table('world', engine)
name continent area population gdp capital tld flag
Afghanistan Asia 652230.0 32225560.0 21992000000.0 Kabul .af //upload.wikimedia.org/wikipedia/commons/9/9a/Flag_of_Afghanistan.svg
Albania Europe 28748.0 2845955.0 13039000000.0 Tirana .al //upload.wikimedia.org/wikipedia/commons/3/36/Flag_of_Albania.svg
Algeria Africa 2381741.0 43000000.0 167555000000.0 Algiers .dz //upload.wikimedia.org/wikipedia/commons/7/77/Flag_of_Algeria.svg
Andorra Europe 468.0 77543.0 3278000000.0 Andorra la Vella .ad //upload.wikimedia.org/wikipedia/commons/1/19/Flag_of_Andorra.svg
Angola Africa 1246700.0 31127674.0 126505000000.0 Luanda .ao //upload.wikimedia.org/wikipedia/commons/9/9d/Flag_of_Angola.svg
Antigua and Barbuda Caribbean 442.0 96453.0 1248000000.0 St. John's .ag //upload.wikimedia.org/wikipedia/commons/8/89/Flag_of_Antigua_and_Barbuda.svg
Argentina South America 2780400.0 44938712.0 637486000000.0 Buenos Aires .ar //upload.wikimedia.org/wikipedia/commons/1/1a/Flag_of_Argentina.svg
Armenia Eurasia 29743.0 2957500.0 11536000000.0 Yerevan .am //upload.wikimedia.org/wikipedia/commons/2/2f/Flag_of_Armenia.svg
Australia Oceania 7692024.0 25690023.0 1408675000000.0 Canberra .au //upload.wikimedia.org/wikipedia/commons/8/88/Flag_of_Australia_%28converted%29.svg
Austria Europe 83871.0 8902600.0 416835000000.0 Vienna .at //upload.wikimedia.org/wikipedia/commons/4/41/Flag_of_Austria.svg

195 rows, truncated to displaylimit of 10

world表一共195行，但由于pandas全局设置了display.max_rows，默认只显示了前10行。 There are a total of 195 rows in world. But only 10 rows are displayed due to the global setting SqlMagic.displaylimit.

目录 Table of Contents

基础题 Fundermentals

Title Link
00 SELECT basics GitHub Gitee
01 SELECT name GitHub Gitee
02 SELECT from World GitHub Gitee
03 SELECT from Nobel GitHub Gitee
04 SELECT within SELECT GitHub Gitee
05 SUM and COUNT GitHub Gitee
06 JOIN GitHub Gitee
07 More JOIN operations GitHub Gitee
08 Using Null GitHub Gitee
08+ Numeric Examples GitHub Gitee
09- Window function GitHub Gitee
09 Self join GitHub Gitee
09+ COVID-19 GitHub Gitee

提高题 Advanced

Title Link
11 Module Feedback GitHub Gitee
12-1 Helpdesk - Easy GitHub Gitee
12-2 Helpdesk - Medium GitHub Gitee
12-3 Helpdesk - Hard GitHub Gitee
13-1 Guest House - Easy GitHub Gitee
13-2 Guest House - Medium GitHub Gitee
13-3 Guest House - Hard GitHub Gitee
14-1 AdventureWorks - Easy GitHub Gitee
14-2 AdventureWorks - Medium GitHub Gitee
14-3 AdventureWorks - Hard GitHub Gitee
14-4 AdventureWorks - Resit GitHub Gitee
15-1 University Timetables - Easy GitHub Gitee
15-2 University Timetables - Medium GitHub Gitee
15-3 University Timetables - Hard GitHub Gitee
15-4 University Timetables - Resit GitHub Gitee
16-1 Musicians - Easy GitHub Gitee
16-2 Musicians - Medium GitHub Gitee
16-3 Musicians - Hard GitHub Gitee
17-0 Dressmaker - Samples GitHub Gitee
17-1 Dressmaker - Easy GitHub Gitee
17-2 Dressmaker - Medium GitHub Gitee
17-3 Dressmaker - Hard GitHub Gitee
18-1 Congestion - Easy GitHub Gitee
18-2 Congestion - Medium GitHub Gitee
18-3 Congestion - Hard GitHub Gitee

挑战 Challege

Title Link
19 White Christmas GitHub Gitee