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.
--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.
--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.
--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)
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.
"Introduction to Python3 by Bill Lubanovic (published by O'Reilly Japan)"
Recommended Posts