MySQL-Python

    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

pymysql

    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

MySQL-Connector

    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>

cx_Oracle

    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

import time

import threading

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.engine.base import Engine

engine = create_engine(

    "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8",

    max_overflow=0,  # 超过连接池大小外最多创建的连接

    pool_size=5,  # 连接池大小

    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错

    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）

)

def task(arg):

    conn = engine.raw_connection()

    cursor = conn.cursor()

    cursor.execute(

        "select * from t1"

    )

    result = cursor.fetchall()

    cursor.close()

    conn.close()

for i in range(20):

    t = threading.Thread(target=task, args=(i,))

    t.start()

#!/usr/bin/env python

# -*- coding:utf-8 -*-

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

from models import Users

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
##############方式一########################

Session = sessionmaker(bind=engine)

# 每次执行数据库操作时，都需要创建一个session

session = Session()

# ############# 执行ORM操作 #############

obj1 = Users(name="alex1")

session.add(obj1)

# 提交事务

session.commit()

# 关闭session

session.close()

######################方式二####################

# 方式二：支持线程安全，为每个线程创建一个session
# - threading.Local
# - 唯一标识
# ScopedSession对象
# self.registry(), 加括号 创建session
# self.registry(), 加括号 创建session
# self.registry(), 加括号 创建session
from greenlet import getcurrent as get_ident

Session = sessionmaker(bind=engine)

session = scoped_session(Session,get_ident)
# session.add
# 操作
session.remove()

#!/usr/bin/env python

# -*- coding:utf-8 -*-

import time

import threading

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

from sqlalchemy import create_engine

from sqlalchemy.sql import text

from db import Users, Hosts

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)

Session = sessionmaker(bind=engine)

session = Session()

# ################ 添加 ################

"""

obj1 = Users(name="wupeiqi")

session.add(obj1)

#批量添加

session.add_all([

    Users(name="wupeiqi"),

    Users(name="alex"),

    Hosts(name="c1.com"),

])

session.commit()

"""

# ################ 删除 ################

"""

session.query(Users).filter(Users.id > 2).delete()

session.commit()

"""

# ################ 修改 ################

"""

session.query(Users).filter(Users.id > 0).update({"name" : "099"})

session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) #字符串

session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")  #数字

session.commit()

"""

# ################ 查询 ################

"""

r1 = session.query(Users).all()

r2 = session.query(Users.name.label('xx'), Users.age).all()   #lable #as xx

r3 = session.query(Users).filter(Users.name == "alex").all()

r4 = session.query(Users).filter_by(name='alex').all()   #如果里面写条件就用filter_by,和上面filter查询是一回事，只是一种不同的方式

r5 = session.query(Users).filter_by(name='alex').first()

r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()  #查询里面如果有动态传参的时候，吧它包在text里面，:value,:name这样的语法后面用.params来进行格式化

r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()  #上面的这个方式也可以用这一种 ，直接可以进行SQL语句的查询

"""

session.close()