[Introduction to Python3, Day 17] Chapter 8 Data Destinations (8.3-8.3.6.1)

8.3 Relational database

See the previous article for details.

"About RDBMS and SQL" https://qiita.com/Taka20200105/items/c58ac4e610954c6d263b

8.3.1 SQL

--SQL falls into two categories. --DDL (Data Definition Language): Create, delete, constrain, and allow table databases. --DML (Data Manipulation Language): Processing such as inserting, selecting, updating, and deleting data

8.3.2 DB-API

--API is a collection of functions to call to access a service. Understanding that opening an API means creating a window and linking it with external software. It's like Dejima in isolation. --DB-API is Python's standard API for accessing RDBMS. --Summary of main functions of DB-API

function meaning
connect() Open a connection to the DB. Arguments can be specified.
cursor() Create a cursor object to manage the query
execute()And execute many() Send one or more SQL commands to the DB.
fechone()、fetchmany()、fetch all() Send one or more SQL commands to the DB.

8.3.3 SQLite

--SQLite stores the DB in a regular file. --Very portable RDBMS.


>>> import sqlite3
#enterprise.Create a DB named db.
>>> conn=sqlite3.connect("enterprise.db")
>>> curs=conn.cursor()
#Creating a table called zoo.
#Primary key string of variable length string called critter
#A sequence of numbers called count
#Floating point column called damages
>>> curs.execute("""CREATE TABLE zoo (critter VARCHAR(20)PRIMARY KEY,count INT,damages FLOAT)""")
<sqlite3.Cursor object at 0x10b215f80>

#The string below execute is the DML command
#INSERT INTO zoo VALUES(〜)Add a line with.
>>> curs.execute("""INSERT INTO zoo VALUES("duck",5,0.0)""")
<sqlite3.Cursor object at 0x10b23d030>
>>> curs.execute("""INSERT INTO zoo VALUES("bear",2,1000.0)""")
<sqlite3.Cursor object at 0x10b23d030>

#It can also be added by the following addition methods.
#Three question marks indicate the planned insertion.
>>> ins="""INSERT INTO zoo (critter,count,damages) VALUES(?,?,?)"""
>>> curs.execute(ins, ("weasel",1,2000.0))
<sqlite3.Cursor object at 0x10b23d030>

#"SELECT *FROM table name"Select all rows and columns with
>>> curs.execute("SELECT * FROM zoo")
<sqlite3.Cursor object at 0x10b23d030>
#Get results
>>> rows=curs.fetchall()
>>> print(rows)
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

#Sort in count order.
>>> curs.execute("SELECT * FROM zoo ORDER BY count")
<sqlite3.Cursor object at 0x10b23d030>
>>> curs.fetchall()
[('weasel', 1, 2000.0), ('bear', 2, 1000.0), ('duck', 5, 0.0)]

#In count order and descending order.
>>> curs.execute("SELECT * FROM zoo ORDER BY count DESC")
<sqlite3.Cursor object at 0x10b23d030>
>>> curs.fetchall()
[('duck', 5, 0.0), ('bear', 2, 1000.0), ('weasel', 1, 2000.0)]

#Selection of the one with the largest damages.
>>> curs.execute("""SELECT * FROM zoo WHERE damages=(SELECT MAX(damages) FROM zoo)""")<sqlite3.Cursor object at 0x10b23d030>
>>> curs.fetchall()
[('weasel', 1, 2000.0)]

#Once you open the connection and cursor, you have to close it when you're done using it.
>>> curs.close
<built-in method close of sqlite3.Cursor object at 0x10b23d030>
>>> conn.close
<built-in method close of sqlite3.Connection object at 0x10b1d4ab0>

8.3.4 MySQL

--MySQL is different from SQLite, it's a real server.

8.3.5 PostgreSQL

--PostgreSQL is a full-fledged open source RDBMS that is more advanced than MySQL.

8.3.6 SQLAIchemy library

--SQL is not the same for all RDBMSs. --DB-API can only support up to the common API level. → Among the libraries that try to fill in the differences between individual DBs, the cross-database Python library SQLAIchemy has a lot of support.

  1. Pool of DB connections, execution of SQL commands, return processing of results.
  2. A SQL expression language that expresses SQL statements as Python expressions.
  3. ORM layer. This layer uses a SQL representation language to connect application code and RDBMS structures.

--dialect + driver: // user: password @ host: port / dbname is the connection string format. --dialect: DB type --driver: The driver you want to use for that DB. --user and password: DB authentication string --host and port: DB server location --dbname: DB on the server to connect to first.

8.3.6.1 Engine layer

--SQLAIchemy will determine the required driver from the connection string. --Portable to other types of DB simply by changing the connection string.


>>> import sqlalchemy as sa
#Open a DB and create a storage area.
#An object called ResultProxy is returned.
#If dbname is omitted, the DB file is stored in memory.
#Host in SQlite strings, port,user,No password required.
>>> conn=sa.create_engine("sqlite://")
#Create a zoo table with 3 columns.
>>> conn.execute("""CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY,count INT,damages FLOAT)""")
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3210>
#Insert f3 data.
>>> ins="""INSERT INTO zoo (critter,count,damages) VALUES(?,?,?)"""
>>> conn.execute(ins,"duck",10,0.0)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3290>
>>> conn.execute(ins,"bear",2,1000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3350>
>>> conn.execute(ins,"weasel",1,2000.0)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee32d0>
#Request all zoo information from DB.
>>> rows=conn.execute("SELECT*FROM zoo")
>>> print(rows)
<sqlalchemy.engine.result.ResultProxy object at 0x108ee3110>
>>> for row in rows:
...     print(row)
... 
('duck', 10, 0.0)
('bear', 2, 1000.0)
('weasel', 1, 2000.0)

Impressions

When I first studied, I thought that MySQL and PostgteSQL were SQL types, but in this review I noticed that they were different.

The above is an RDBMS, not a language. And many DBs have each dialect implemented, and the SQL AIchemy library came out to fill in the differences between the dialects. It is wise to judge by the connection string without installing the driver for each DB.

References

"Introduction to Python3 by Bill Lubanovic (published by O'Reilly Japan)"

Recommended Posts

[Introduction to Python3, Day 17] Chapter 8 Data Destinations (8.3-8.3.6.1)
[Introduction to Python3 Day 19] Chapter 8 Data Destinations (8.4-8.5)
[Introduction to Python3 Day 18] Chapter 8 Data Destinations (8.3.6.2 to 8.3.6.3)
[Introduction to Python3 Day 13] Chapter 7 Strings (7.1-7.1.1.1)
[Introduction to Python3 Day 14] Chapter 7 Strings (7.1.1.1 to 7.1.1.4)
[Introduction to Python3 Day 15] Chapter 7 Strings (7.1.2-7.1.2.2)
[Introduction to Python3 Day 21] Chapter 10 System (10.1 to 10.5)
[Introduction to Python3 Day 12] Chapter 6 Objects and Classes (6.3-6.15)
[Introduction to Python3 Day 22] Chapter 11 Concurrency and Networking (11.1 to 11.3)
[Introduction to Python3 Day 11] Chapter 6 Objects and Classes (6.1-6.2)
[Introduction to Python3 Day 23] Chapter 12 Become a Paisonista (12.1 to 12.6)
[Introduction to Python3 Day 20] Chapter 9 Unraveling the Web (9.1-9.4)
[Introduction to Python3 Day 8] Chapter 4 Py Skin: Code Structure (4.1-4.13)
[Technical book] Introduction to data analysis using Python -1 Chapter Introduction-
[Introduction to Python3 Day 1] Programming and Python
[Introduction to Python3 Day 3] Chapter 2 Py components: Numbers, strings, variables (2.2-2.3.6)
[Introduction to Python3 Day 2] Chapter 2 Py Components: Numbers, Strings, Variables (2.1)
[Introduction to Python3 Day 4] Chapter 2 Py Components: Numbers, Strings, Variables (2.3.7-2.4)
Introduction to Effectiveness Verification Chapter 1 in Python
[Introduction to Data Scientists] Basics of Python ♬
[Introduction to Python3 Day 7] Chapter 3 Py Tools: Lists, Tuples, Dictionaries, Sets (3.3-3.8)
[Introduction to Python3 Day 5] Chapter 3 Py Tools: Lists, Tuples, Dictionaries, Sets (3.1-3.2.6)
[Introduction to Python3 Day 10] Chapter 5 Py's Cosmetic Box: Modules, Packages, Programs (5.4-5.7)
[Introduction to Python3 Day 9] Chapter 5 Py's Cosmetic Box: Modules, Packages, Programs (5.1-5.4)
[Introduction to Python3 Day 6] Chapter 3 Py tool lists, tuples, dictionaries, sets (3.2.7-3.2.19)
Introduction to Python language
Introduction to OpenCV (python)-(2)
Introduction to effectiveness verification Chapter 3 written in Python
[Introduction to Python] How to handle JSON format data
Introduction to Effectiveness Verification Chapter 2 Written in Python
Python for Data Analysis Chapter 4
[Chapter 5] Introduction to Python with 100 knocks of language processing
Reading Note: An Introduction to Data Analysis with Python
Introduction to serial communication [Python]
[Chapter 3] Introduction to Python with 100 knocks of language processing
[Chapter 2] Introduction to Python with 100 knocks of language processing
[Introduction to Python] <list> [edit: 2020/02/22]
Python for Data Analysis Chapter 2
Introduction to Python (Python version APG4b)
An introduction to Python Programming
Introduction to Python For, While
Python for Data Analysis Chapter 3
[Chapter 4] Introduction to Python with 100 knocks of language processing
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
20200329_Introduction to Data Analysis with Python Second Edition Personal Summary
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
[Introduction to Python] Combine Nikkei 225 and NY Dow csv data
[Python] Introduction to graph creation using coronavirus data [For beginners]
[Introduction to Python] How to get data with the listdir function
[Introduction to Udemy Python 3 + Application] 58. Lambda
[Introduction to Udemy Python 3 + Application] 31. Comments
[Python] How to FFT mp3 data
Data Scientist Training Course Chapter 2 Day 2
Introduction to Python Numerical Library NumPy
Practice! !! Introduction to Python (Type Hints)
Data Scientist Training Course Chapter 3 Day 3
[Introduction to Python] <numpy ndarray> [edit: 2020/02/22]
Data Scientist Training Course Chapter 4 Day 1
[Introduction to Udemy Python 3 + Application] 57. Decorator
Introduction to Python Hands On Part 1
Data Scientist Training Course Chapter 3 Day 1 + 2