setup or import sqlite3＆postgresql

sqlite3 Win shell

1. 下载 sqlite地址 ：https://sqlite.org/download.html

2. 下载 sqlite-tools-win32-*.zip （或x64）和 sqlite-dll-win32-*.zip 压缩文件，解压后放入同一文件夹，如c:\sqlite3 。

3. 添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令。

sqlite3 python module (pip3 install sqlite3)

postgreSQL

下载 postgreSQL

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

windows 打开 pgAdmin 4

postgresql in python module

以通过anaconda安装pip,确保pip已升级最高版本

cmd(administrator)

18.1(2018.11)

通过pip3 安装psycopy2 module

以便 jupyter 或者 python3 中 使用

sqlite3

connect to database (python)

is aconnection instance,maintains the connection to the database.

While we use the Connection class to represent the database we’re working with, we use the Cursor class to:

1. Run a query against the database

2. Parse the results from the database

3. Convert the results to native Python objects

4. Store the results within the Cursor instance as a local variable

After running a query and converting the results to a list of tuples, the Cursor instance stores the list as a local variable.

creat a cursor instance

shortcut for runing a query

fetch a specific number of results

close database connection

practice again

GROUP BY & DISTINCT

The GROUP BY SQL statement allows us to compute summary statistics by “group,” or unique value. When we use this statement, SQL creates a group for each unique value in a column or set of columns (the same values we get when we use the DISTINCT statement), and then does the calculations for them.

When we want to filter on a column generated by a GROUP BY query, we can use the HAVING statement.

HAVING

ROUND

CAST

Subqueries run first

Subquery in SELECT

IN

Integrating a subquery with the outer query