[ Python 文章收集 ] SQLAlchemy Core - Connecting to Database (1)

Source From Here 
Environment Setting 
Here we will use PostgreSQL to demonstrate the usage of SQLAlchemy. In order to have a PostgreSQL DB for testing, you can leverage the exist docker image: 

// Launch a docker container as postgres DB service and open port 5432
# docker run --rm --name postgres_test -p 5432:5432 -e POSTGRES_PASSWORD= -d postgres
# ds --format="{{.ID}}:{{.Names}}" // Make sure the docker container is running
0558c32602a7:postgres_test

# psql -U postgres -h localhost // Connect DB
Password for user postgres: 
psql (9.2.24, server 11.2 (Debian 11.2-1.pgdg90+1))
WARNING: psql version 9.2, server version 11.0.
Some psql features might not work.
Type "help" for help.

postgres=# CREATE DATABASE testdb; // Create database for testing

postgres=# \c testdb // Switch to database 'testdb'
psql (9.2.24, server 11.2 (Debian 11.2-1.pgdg90+1))
WARNING: psql version 9.2, server version 11.0.
Some psql features might not work.
You are now connected to database "testdb" as user "postgres".
testdb=#

Connecting to Database 
Engine class connects a Pool and Dialect together to provide a source of database connectivity and behavior. An object of Engine class is instantiated using the create_engine() function: 

>>> from sqlalchemy import create_engine
>>> db_string = "postgresql://postgres:@localhost/testdb" // Replace with password given while starting the container
>>> engine = create_engine(db_string, echo=True)

To specifically mention DB-API to be used for connection, the URL string takes the form as follows − 

view plaincopy to clipboardprint?

1. dialect[+driver]://user:password@host/dbname  

For example, if you are using PyMySQL driver with MySQL, use the following command − 

view plaincopy to clipboardprint?

1. mysql+pymysql://:@/  

The echo flag is a shortcut to set up SQLAlchemy logging, which is accomplished via Python’s standard logging module. In the subsequent chapters, we will learn all the generated SQLs. To hide the verbose output, set echo attribute to None. Other arguments to create_engine() function may be dialect specific. 

Creating Table 
Let us now discuss how to use the create table function. 

The SQL Expression Language constructs its expressions against table columns. SQLAlchemy Column object represents a column in a database table which is in turn represented by a Table object. Metadata contains definitions of tables and associated objects such as index, view, triggers, etc. 

Hence an object of MetaData class from SQLAlchemy is a collection of Table objects and their associated schema constructs. It holds a collection of Table objects as well as an optional binding to an Engine or Connection. 

view plaincopy to clipboardprint?

1. from sqlalchemy import MetaData  
2. meta = MetaData()  

Next, we define our tables all within above metadata catalog, using the Table construct, which resembles regular SQL CREATE TABLE statement. An object of Table class represents corresponding table in a database; Columnobject represents a column in a database table. Constructor takes name, type and other parameters such as primary_key, autoincrement and other constraints. 

SQLAlchemy matches Python data to the best possible generic column data types defined in it. For more, please refer to Column and Data Types. To create a students table in college database, use the following snippet: 

view plaincopy to clipboardprint?

1. from sqlalchemy import Table, Column, Integer, String, MetaData  
2. meta = MetaData()  
3.   
4. students = Table(  
5.    'students', meta,   
6.    Column('id', Integer, primary_key = True),   
7.    Column('name', String),   
8.    Column('lastname', String),   
9. )  

The create_all() function uses the engine object to create all the defined table objects and stores the information in metadata: 

view plaincopy to clipboardprint?

1. meta.create_all(engine)  

Complete code is given below which will create a Postgresql database testdb with a students table in it: 

view plaincopy to clipboardprint?

1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
2.   
3. db_string = "postgresql://postgres:@localhost/testdb"  
4. engine = create_engine(db_string, echo = True)  
5. meta = MetaData()  
6.   
7. students = Table(  
8.    'students', meta,   
9.    Column('id', Integer, primary_key = True),   
10.    Column('name', String),   
11.    Column('lastname', String),  
12. )  
13. meta.create_all(engine)  

Because echo attribute of create_engine() function is set to True, the console will display the actual SQL query for table creation as follows: 

view plaincopy to clipboardprint?

1. CREATE TABLE students (  
2.         id SERIAL NOT NULL,  
3.         name VARCHAR,  
4.         lastname VARCHAR,  
5.         PRIMARY KEY (id)  
6. )  

To double confirm that the table students is created, you can use psql command by: 

# psql -U postgres -h localhost testdb // Connect DB

view plaincopy to clipboardprint?

1. testdb=# \d  
2.                List of relations  
3. Schema |      Name       |   Type   |  Owner  
4. --------+-----------------+----------+----------  
5. public | students        | table    | postgres  
6. public | students_id_seq | sequence | postgres  
7. (2 rows)  
8.   
9. testdb=# \d+ students  
10.                                                    Table "public.students"  
11.   Column  |       Type        |                       Modifiers                       | Storage  | Stats target | Description  
12. ----------+-------------------+-------------------------------------------------------+----------+--------------+-------------  
13. id       | integer           | not null default nextval('students_id_seq'::regclass) | plain    |              |  
14. name     | character varying |                                                       | extended |              |  
15. lastname | character varying |                                                       | extended |              |  
16. Indexes:  
17.     "students_pkey" PRIMARY KEY, btree (id)  
18. Has OIDs: no  

Supplement 
* PostgreSQL - CREATE Database 
* PostgreSQL - CREATE Table 
* docker 命令解析之`PS`