#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Author: Janice Cheng

"""
1) 通过 create_engine 方法创建 MySQL 数据库的连接，create_engine("url") 接受一个 URL 连接:
    1.1) MySQL-Python: mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
    1.2) pymysql: mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
    1.3) MySQL-Connector: mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
    1.4) cx_Oracle: oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
    1.5) PostgreSQL: postgresql+pg8000://<user>:<password>@<host>/<db>

    Details: http://docs.sqlalchemy.org/en/latest/dialects/index.html

2) 成功创建连接之后就可以运行原生 SQL 来对数据库进行操作 engine.execute("sql....")，SQL包括 INSERT INTO, SELECT, UPDATE, DELETE FROM
3) 然后可以调用 cursor.fetchone()/ fetchall()/ fetchmany() 来取实际数据，它是一个列表里嵌套元组 [()]
    3.1) cursor.fetchone()
    3.2) cursor.fetchall()
    3.3) cursor.fetchmany()

"""
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://myuser:mypass@172.16.201.134:3306/s13", max_overflow=5)

# SELCT SQL: SELECT * FROM dm_person;
cursor = engine.execute("SELECT * FROM dm_person")

# INSERT SQL: INSERT INTO dm_person (name,age) values ('alexander','28');
# cursor = engine.execute("INSERT INTO dm_person (name,age) values (%s,%s)",[('alexander',28,),('jennifer',30,)])

# UPDATE SQL: Select * from dm_person;

# record_row = cursor.fetchone()
record_row = cursor.fetchall() #列表里嵌套元组
# record_row = cursor.fetchmany(5)

# print(record_row)
print(('sid','name','age',))
for record in record_row:
    print(record)


