Python SQLAlchemy使用方法及注意事项

一、全表导入

数据是以Dataframe形式存储，直接全表导入，不用一条一条写入，方便快捷

from sqlalchemy import create_engine
import pandas as pd
# 不同数据库使用不同引擎
# mysql 需安装pymysql
engine = create_engine(mysql+pymysql://用户名:密码@hostIP:端口号/数据库名)
# postgre 需安装psycopg2
engine = create_engine(postgresql+psycopg2://用户名:密码@hostIP:端口号/数据库名)
# oracle 需安装cx_Oracle
engine = create_engine(oracle+cx_oracle://用户名:密码@hostIP:端口号/数据库名)
# sqlserver 需安装pymssql（sqlserver也可使用pyodbc，但未成功）
engine = create_engine(mssql+pymssql://用户名:密码@IP[:端口号]/数据库名)
# engine = create_engine(mssql+pyodbc://用户名:密码@IP[:端口号]/数据库名)

# 指定数据表字段类型，常见的数据类型均可指定
dtype = {
          
   
	a: sqlalchemy.types.VARCHAR(100),
    b: sqlalchemy.types.DATE,
    c: sqlalchemy.types.DECIMAL(10, 2),
    d: sqlalchemy.types.TEXT,
    e: sqlalchemy.types.TIMESTAMP
        }

put_data是导入的数据，Dataframe格式
table_name是库中数据表名称
egine是上面定义的数据库引擎
index是put_data的列索引是否导入数据库中
schema是模式名，postgre、oracle等可能会使用
if_existe是数据入库形式。
	fail：如果表存在，啥也不做
	replace：如果表存在，删了表，再建立一个新表，把数据插入【自测发现重建的新表会丢失注释、主键等信息】
	append：如果表存在，把数据插入，如果表不存在创建一个表
dtype是字段类型

pd.io.sql.to_sql(put_data, table_name, engine, index=False, schema=schema_name, if_exists=append, dtype=dtype)

注意事项： 1、如果是想实现全量数据覆盖原有数据，建议先执行truncate table再全量导表（if_existe=‘append’）避免使用if_existe=‘replace’ 2、如果密码中包含@或特殊字符，直接用字符串拼接会导致create_engine解析出错，可使用特殊符号编码，如下

from urllib import parse
pwd=xxx@xxx
engine = create_engine(mssql+pymssql://user_name:%s@ip/database_name % parse.quote(pwd))

3、Oracle 19c用上述方法链接会报错 ORA-12505: TNS:listener does not currently know of SID given in connect descriptor 可以直接用cx_Oracle进行链接，如下：

import cx_Oracle
conn = cx_Oracle.connect(user=用户名, password=密码, dsn="ip:端口号/数据库名")

二、逐条导入

逐条导入方法也较简单，写好sql，执行即可

sql = "insert into a.table_b (acol,bcol) values(acol value,bcol value)"
engine = create_engine(postgresql+psycopg2://user_name:password@ip:port/schema)
Session = sessionmaker(bind=engine)
session = Session()
session.execute(sql)

注意事项：sql中如果字符串或文本类型的字段有英文状态下的冒号（:），SQLAlchemy会默认将它当作参数传递的特殊符号，如果实际并无参数传递，此时会报异常：(sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter ****。 解决办法：使用反斜杠转义 text = text.replace(:, :)