[1024]python sqlalchemy中create_engine用法

用法

engine = create_engine('dialect+driver://username:password@host:port/database')

dialect：数据库类型 driver：数据库驱动选择 username：数据库用户名 password： 用户密码 host：服务器地址 port：端口 database：数据库

PostgreSQL

default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

psycopg2

engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

pg8000

engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

More notes on connecting to PostgreSQL at PostgreSQL .

MySQL

default
engine = create_engine('mysql://scott:tiger@localhost/foo')

mysql-python

engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

MySQL-connector-python

engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

OurSQL

engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

More notes on connecting to MySQL at MySQL .

Oracle

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

More notes on connecting to Oracle at Oracle .

Microsoft SQL Server

engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

pymssql

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

SQLite

engine = create_engine('sqlite:///foo.db')
engine = create_engine('sqlite:absolute/path/to/foo.db')

使用

• 下面mysql作为例子

yconnect = create_engine('mysql+mysqldb://root:password@host:port/db?charset=utf8')  
pd.io.sql.to_sql(DataResultDF,'tablename', yconnect, schema='db', if_exists='append')   

• 创建表结构

使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象，之后通过SQL Expression Language将该对象转换成SQL语句，然后通过 ConnectionPooling 连接数据库，再然后通过 Dialect 执行SQL，并获取结果。

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
import MySQLdb
#创建数据库连接
engine = create_engine("mysql+mysqldb://liuyao:liuyao@121.42.195.15:3306/db_name", max_overflow=5)
# 获取元数据
metadata = MetaData()
# 定义表
user = Table('user', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
color = Table('color', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(20)),
#将dataframe 添加到 tmp_formidinfo 如果表存在就添加，不存在创建并添加 
pd.io.sql.to_sql(DataResultDF,'tmp_formidinfo',engine, schema='db_name', if_exists='append')                
# 执行sql语句
engine.execute(
    "INSERT INTO db_name.color(id, name) VALUES ('1', 'liuyao');"
result = engine.execute('select * from color')
print(result.fetchall())
-----------------------------------------------------------------------------------
sql = 'select * from customer2018;'
df = pd.read_sql_query(sql, engine)
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
print(df)

create_engine 还有很多可选参数，这里介绍几个重要的参数

engine=create_engine('mysql://user:password@localhost:3306/test?charset=utf8mb4',