[ Python 文章收集 ] SQLAlchemy Core - SQL Expressions (2)

Source From Here 
SQL Expressions 
SQL expressions are constructed using corresponding methods relative to target table object. For example, the INSERT statement is created by executing insert() method as follows: 

>>> ins = students.insert()
>>> ins

>>> str(ins)
'INSERT INTO students (id, name, lastname) VALUES (%(id)s, %(name)s, %(lastname)s)'

It is possible to insert value in a specific field by values() method to insert object. The code for the same is given below: 

>>> ins = students.insert().values(name='lee', lastname='john')
>>> str(ins)
'INSERT INTO students (id, name, lastname) VALUES (%(id)s, %(name)s, %(lastname)s)'

The SQL echoed on Python console doesn’t show the actual value (‘john’ in this case). Instead, SQLALchemy generates a bind parameter which is visible in compiled form of the statement: 

>>> ins.compile().params
{'id': None, 'name': 'lee', 'lastname': 'john'}

Executing Expression 
In order to execute the resulting SQL expressions, we have to obtain a connection object representing an actively checked out DBAPI connection resource and then feed the expression object as shown in the code below: 

view plaincopy to clipboardprint?

1. conn = engine.connect()  

The following insert() object can be used for execute() method: 

>>> ins = students.insert().values(name = 'Lee', lastname = 'John')
>>> result = conn.execute(ins)
2019-03-05 22:11:50,246 INFO sqlalchemy.engine.base.Engine INSERT INTO students (name, lastname) VALUES (%(name)s, %(lastname)s) RETURNING students.id
2019-03-05 22:11:50,246 INFO sqlalchemy.engine.base.Engine {'name': 'Lee', 'lastname': 'John'}
2019-03-05 22:11:50,251 INFO sqlalchemy.engine.base.Engine COMMIT

Following is the entire snippet that shows the execution of INSERT query using SQLAlchemy’s core technique: 

view plaincopy to clipboardprint?

1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
2.   
3. db_string = "postgresql://postgres:john7810@localhost/testdb"  
4. engine = create_engine(db_string, echo = True)  
5. meta = MetaData(bind=engine)  
6.   
7. students = Table('students', meta, Column('id', Integer, primary_key = True),  
8.                  Column('name', String), Column('lastname', String))  
9.   
10. ins = students.insert().values(name = 'Lee', lastname = 'John')  
11. conn = engine.connect()  
12. result = conn.execute(ins)  

The result can be verified by opening the database using psql: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM students;  
2. id | name | lastname  
3. ----+------+----------  
4.   1 | Lee  | John  
5. (1 row)  

Selecting Rows 
The select() method of table object enables us to construct SELECT expression: 

>>> s = students.select()
>>> str(s)
'SELECT students.id, students.name, students.lastname \nFROM students'

We can use this select object as a parameter to execute() method of connection object as shown in the code below: 

>>> result = conn.execute(s)
2019-03-05 22:20:32,484 INFO sqlalchemy.engine.base.Engine SELECT students.id, students.name, students.lastname
FROM students
2019-03-05 22:20:32,484 INFO sqlalchemy.engine.base.Engine {}

The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method: 

>>> result.__class__

>>> for row in result.fetchone():
... print(row)
...
1
Lee
John

The complete code to print all rows from students table is shown below: 
- demo3.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
2.   
3. db_string = "postgresql://postgres:john7810@localhost/testdb"  
4. engine = create_engine(db_string, echo = True)  
5. meta = MetaData(bind=engine)  
6.   
7. students = Table('students', meta, Column('id', Integer, primary_key = True),  
8.                  Column('name', String), Column('lastname', String))  
9.   
10. conn = engine.connect()  
11. s = students.select()  
12. result = conn.execute(s)  
13. for row in result:  
14.     print("id={}, name={}; lastname={}".format(row[0], row[1], row[2]))  

We have inserted a few fake testing data. So our output will look like: 

id=1, name=Lee; lastname=John
id=2, name=Lin; lastname=Mary
id=3, name=Ravi; lastname=Kapoor
id=4, name=Rajiv; lastname=Khanna
id=5, name=Komal; lastname=Bhandari
id=6, name=Abdul; lastname=Sattar
id=7, name=Priya; lastname=Rajhans

The WHERE clause of SELECT query can be applied by using Select.where(). For example, if we want to display rows with id >2: 

view plaincopy to clipboardprint?

1. s = students.select().where(students.c.id>2)  
2. result = conn.execute(s)  
3.   
4. for row in result:  
5.    print (row)  

Here c attribute is an alias for column. Following output will be displayed on the shell: 

(3, 'Ravi', 'Kapoor')
(4, 'Rajiv', 'Khanna')
(5, 'Komal', 'Bhandari')
(6, 'Abdul', 'Sattar')
(7, 'Priya', 'Rajhans')

Here, we have to note that select object can also be obtained by select() function in sqlalchemy.sql module. The select() function requires the table object as argument: 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import select  
2. students = meta.tables['students']  
3. s = select([students])  
4. result = conn.execute(s)  

Supplement 
* SQLAlchemy Document - Insert, Updates, Deletes