Python使用pymysql和cx_Oracle插入数据的对比

Python使用pymysql和cx_Oracle插入数据的对比

使用Python向MySQL和Oracle数据库插入数据，常用的数据类型字符串，日期，数字，insert命令的拼接异同之处进行对比。

1. 代码

import cx_Oracle
import pymysql
import datetime
import os

# for Linux
os.environ["ORACLE_HOME"] = '/app/oracle/product/12.2.0/db_home'
os.environ["LD_LIBRARY_PATH"] = '/app/oracle/product/12.2.0/db_home'


def save_oracle():
    try:
        connection = cx_Oracle.connect('username/password@hostname:port/service_name') 
        cursor = db.cursor()
        cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")
        # ORACLE error occurred: ORA-01861: literal does not match format string

        sqlstring = "insert into student (name ,birthday ,age) values ('%s', '%s', %s)"    
        #sqlstring = "insert into student (name ,birthday ,age) values ('%s', to_date('%s','yyyy-mm-dd hh24:mi:ss'), %s)"
        values = ('Tom', datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), '26')
        # sql和value分开，使用参数化查询来避免SQL注入的风险。
        
        # oracle不支持 cursor.execute(sqlstring,values) ，报错 ORA-01036: illegal variable name/number
        # 只能直接拼接sql命令，
        sqlstring = sqlstring % values
        
        #print (sqlstring)
        cursor.execute(sqlstring)
        db.commit()

    except Exception as e:
        print(f"ORACLE error occurred: {e}")
        
    finally:
        close_db_connection()
        cursor.close()
        db.close()
        
def save_mysql():
    try:
        db = pymysql.connect(host='localhost', port=3306, user='root', password='password', database='mydatabase')
        cursor = db.cursor()

        sqlstring = "insert into student (name ,birthday ,age) values (%s, %s, %s)"
        values = ('Tom', datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 26)
        
        # sql和value分开，使用参数化查询来避免SQL注入的风险。
        cursor.execute(sqlstring, values)
        db.commit()
    except Exception as e:
        print(f"MySQL error occurred,sharpe: {e}")
    finally:
        cursor.close()
        db.close()

if __name__ == "__main__":
    save_oracle()        
    save_mysql()

2. 说明

插入数据拼接，都用%s占位符，进行数据替代。

（1）Oracle

• 日期：
  第一种方法：
  oracle 需要用 ‘%s’ 占位符 ，需要设置会话的日期格式，才能执行，当然如果默认的日期格式与插入日期格式一致，可以不设置。

cursor.execute(“ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’”)
否则报错：
ORACLE error occurred: ORA-01861: literal does not match format string

第二种方法：
直接用to_date函数把占位符调整格式，就不用设置会话日期格式了。

sqlstring = “insert into student (name ,birthday ,age) values (‘%s’, to_date(‘%s’,‘yyyy-mm-dd hh24:mi:ss’), %s)”

• 字符串
  oracle字符串，一定要用单引号括上%s ，视为字符串。

• 数值
  oracle不用单引号括上%s ，但是使用单引号括上%s ，能自动转换，也不报错。

• 执行
  oracle不支持拼接的执行方式

cursor.execute(sqlstring,values)

报错 ORA-01036: illegal variable name/number
只能替代合并后，一个字符串执行

sqlstring = sqlstring % values

（2）MySQL

• 数值、日期、字符串，都用%s占位符，替代。数据库能够自动转换
• 执行：
  可以分开语句和替代值执行，避免sql注入风险。

cursor.execute(sqlstring, values)