Python's path to ORM SQLAlchemy

1. Installation

# pip Install SQLAlchemy
# pip Install Pymysql

2. Creating an engine

>>> from SQLAlchemy import create_engine
>>> engine = Create_engine (' Mysql+pymysql://username:[email protected]_host:db_port/db_name ', pool_recycle =3600)
pool_recycle Specifies the connection pool recovery time.

Once the engine is initialized, you can connect to the database.
3. Connect to the database

>>> connection = Engine.connect ()

4. Metadata

>>> from SQLAlchemy import MetaData
>>> metadata = metadata ()

5. Defining tables

>>> from SQLAlchemy import Table, Column, Integer, Numeric, String, ForeignKey, DateTime
>>> from datetime import datetime
>>> users = Table (' Users ', metadata,
... Column (' user_id ', Integer (), primary_key=true),
... Column (' username ', String (+), Nullable=false, Unique=true),
... Column (' email_address ', String (255), Nullable=false),
... Column (' Phone ', String (), Nullable=false),
... Column (' Password ', String (+), nullable=false),
... Column (' created_on ', DateTime (), Default=datetime.now),
... Column (' updated_on ', DateTime (), Default=datetime.now, Onupdate=datetime.now)
... )

6. Create a table

>>> Metadata.create_all (Engine)

7. Inserting data

>>> ins = Users.insert (). VALUES (
... user_id=8888,
... username= ' 111cn.net ',
... email_address = ' [email protected] ',
... phone = 12345678901,
... password = ' www.111cn.net '
... )
>>> Print INS
INSERT into Users (user_id, username, email_address, phone, password, created_on, updated_on) VALUES (: user_id,: Username, : email_address,:p Hone,:p assword,: created_on,: updated_on)
>>> ins.compile (). Params
{' username ': ' 111cn.net ', ' user_id ': 8888, ' phone ': 12345678901, ' created_on ': None, ' updated_on ': none, ' password ': ' www .111cn.net ', ' email_address ': ' [email protected] '}

8. Implementation

>>> result = Connection.Execute (INS)
>>> Result.inserted_primary_key
[8888]

9. Insert more than one

>>> ins = Users.insert ()
>>> Multi_data = [
... {
... ' user_id ': 1,
... ' username ': ' U1 ',
... ' email_address ': ' [email protected] ',
... ' Phone ': 12345678901,
... ' Password ': ' Www.111cn.net '
... },
... {
... ' user_id ': 2,
... ' username ': ' U2 ',
... ' email_address ': ' [email protected] ',
... ' Phone ': 12345678901,
... ' Password ': ' Www.111cn.net '
... }
... ]
>>> result = Connection.Execute (INS, multi_data)
>>> Result.rowcount
2

10. Enquiry

>>> from SQLAlchemy import select
>>> s = select ([users])
>>> Print STR (s)
SELECT users.user_id, Users.username, users.email_address, Users.phone, Users.password, users.created_on, users.updated_on 
from users
>>> RP = Connection.Execute (s)
>>> results = Rp.fetchall ()
>>> Print Results
[(1, ' U1 ', ' [email protected] ', ' 12345678901 ', ' www.111cn.net ', Datetime.datetime (6, 5), Datetime.datetime (6, 5)), (+ 2, ' U2 ', ' [email protected] ') , ' 12345678901 ', ' www.111cn.net ', Datetime.datetime (6, 5), Datetime.datetime (2016, 6, 22, 11, 26, 5)), (8888, ' 111cn.net ', ' [email protected] ', ' 12345678901 ', ' www.111cn.net ', Datetime.datetime (2016, 6, 22, 11, 13, 32 ), Datetime.datetime (6, one, one, one, all))] [

]

>>> First_row = results[0]
>>> Print First_row
(1, ' U1 ', ' [email protected] ', ' 12345678901 ', ' www.111cn.net ', Datetime.datetime (6, 5), Datetime.datet IME (2016, 6, 22, 11, 26, 5))
>>> First_row[1]
' U1 '
>>> First_row.phone
' 12345678901 '
>>> first_row[users.c.user_id]

>>> RP = Connection.Execute (s)
>>> for record in RP:
... print Record.username
...
U1
U2
111cn.net

>>> s = select ([users.c.user_id, Users.c.username])
>>> RP = Connection.Execute (s)
>>> Print Rp.keys ()
[' user_id ', ' username ']
>>> Print Rp.fetchone ()
(8888, ' 111cn.net ')
>>> Print Rp.fetchone ()
(1, ' U1 ')
>>> Print Rp.fetchone ()
(2, ' U2 ')
>>> Print Rp.fetchone ()
None

11. Sorting

>>> s = select ([users.c.user_id, Users.c.username])
>>> s = s.order_by (users.c.user_id)
>>> Print str (s)
SELECT users.user_id, Users.username
From the users ORDER by users.user_id
>>> RP = Connection.Execute (s)
>>> for I in RP:
... print (' {}-{} '. Format (i.user_id, I.username))
...
1-u1
2-u2
8888-111cn.net

>>> s = select ([users.c.user_id, Users.c.username])
>>> s = s.order_by (desc (users.c.user_id))
>>> RP = Connection.Execute (s)
>>> print ([' {}-{} '. Format (i.user_id, i.username) for I in RP])
[' 8888-111cn.net ', ' 2-u2 ', ' 1-u1 ']

>>> s = select ([users.c.user_id, Users.c.username]). where (Users.c.user_id.in_ ([up]))
>>> RP = Connection.Execute (s)
>>> print ([' {}-{} '. Format (i.user_id, i.username) for I in RP])
[' 1-u1 ', ' 2-u2 ']

12. Update

>>> from SQLAlchemy Import update
>>> u = Update (users). WHERE (Users.c.username = = ' U1 ')
>>> u = u.values (phone=00000000000)
>>> Print str (u)
UPDATE users SET phone=:p hone, updated_on=:updated_on WHERE users.username =: username_1
>>> result = Connection.Execute (u)
>>> Print Result
<sqlalchemy.engine.result.resultproxy Object at 0x7f6839f1a290>
>>> Print Result.rowcount
1
>>> s = select ([users]). WHERE (Users.c.username = = ' U1 ')
>>> result = Connection.Execute (s). First ()
>>> Print Result
(1, ' U1 ', ' [email protected] ', ' 0 ', ' www.111cn.net ', Datetime.datetime (6, 5), Datetime.datetime (2016, 6, 22, 13, 51, 16))
>>> Print Result.keys ()
[' user_id ', ' username ', ' email_address ', ' phone ', ' password ', ' created_on ', ' updated_on ']
>>> for key in Result.keys ():
... print (' {: >20}: {} '. Format (key, Result[key]))
...
User_id:1
Username:u1
email_address: [Email protected]
phone:0
Password:www.111cn.net
Created_on:2016-06-22 11:26:05
Updated_on:2016-06-22 13:51:16
13. Delete

>>> Print str (u)
DELETE from users WHERE users.username =: username_1
>>> s = select ([users]). WHERE (Users.c.username = = ' U1 ')
>>> result = Connection.Execute (s). Fetchall ()
>>> Print Result
[]

Python's path to ORM SQLAlchemy