spyder编译器,python3.6.2版本
oracle查询数据时,一直报错
import pandas as pd
import cx_Oracle
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
os.environ[‘NLS_LANG’] = ‘SIMPLIFIED CHINESE_CHINA.UTF8’
engine=create_engine(‘oracle+cx_oracle://test:12345678@localhost/orcl’)
conn=engine.connect()
DBSession=sessionmaker(bind=engine)
session=DBSession()
conn.execute(‘drop table test.test4’)
conn.execute(‘create table test4(name varchar2(10),gender varchar2(10),age varchar2(10))’)
test_data=pd.DataFrame([[‘小东’,’男’,15],[‘小花’,’女’,12],[‘xiaoli’,’nan’,16]],
index=[1,2,3],columns=[‘name’,’gender’,’age’])
test_data.to_sql(‘test4’,engine,if_exists=’append’,index=False)
sql=’select * from test4 where name=xiaoli’
sql=’select * from test4 where NAME=xiaoli’
sql=’select * from test4 where name=’xiaoli”
sql=’select * from test4 where NAME=’xiaoli”
sql=’select * from test4 where name=”xiaoli”’
sql=’select * from test4 where NAME=”xiaoli”’
sql=’select * from test4 where to_char(“name”)=xiaoli’
这几种方法都试了,一直报错,但是
sql=’select * from test4 where age=12’
就正常出结果
也就是说只要查询数字就能出结果,查询字母和汉字都报下面的错
z3=pd.read_sql(sql,con=engine)
print(z3)
报错
DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: “XIAOLI”: 标识符无效 [SQL: ‘select * from test4 where to_char(“name”)=xiaoli’] (Background on this error at: http://sqlalche.me/e/4xp6)