Insert data from file into database

Question

I have a .sql file with multiple insert statements ( 1000 + ) and I want to run the statements in this file into my Oracle database.

For now, im using a python with odbc to connect to my database with the following:

import pyodbc
from ConfigParser import SafeConfigParser
def db_call(self, cfgFile, sql):
 parser = SafeConfigParser()
 parser.read(cfgFile)
 dsn = parser.get('odbc', 'dsn')
 uid = parser.get('odbc', 'user')
 pwd = parser.get('odbc', 'pass')
 try:
 con = pyodbc.connect('DSN=' + dsn + ';PWD=' + pwd + ';UID=' + pwd)
 cur = con.cursor()
 cur.execute(sql)
 con.commit()
 except pyodbc.DatabaseError, e:
 print 'Error %s' % e
 sys.exit(1)
 finally:
 if con and cur:
 cur.close()
 con.close()
with open('theFile.sql','r') as f:
 cfgFile = 'c:\\dbinfo\\connectionInfo.cfg'
 #here goes the code to insert the contents into the database using db_call_many 
 statements = f.read()
 db_call(cfgFile,statements)

But when i run it i receive the following error:

pyodbc.Error: ('HY000', '[HY000] [Oracle][ODBC][Ora]ORA-00911: invalid character\n (911) (SQLExecDirectW)')

But all the content of the file are only:

INSERT INTO table (movie,genre) VALUES ('moviename','horror');

Edit

Adding print '<{}>'.format(statements) before the db_db_call(cfgFile,statements) i get the results(100+):

<INSERT INTO table (movie,genre) VALUES ('moviename','horror');INSERT INTO table (movie,genre) VALUES ('moviename_b','horror');INSERT INTO table (movie,genre) VALUES ('moviename_c','horror');>

Thanks for your time on reading this.

• Jon Clements: The problem is in whatever statements has... Have you looked at that? (ie: it's not interpretable by the SQL engine at point)
• thclpr: editing main question with more info.
• unutbu: Any difference if you use with open('theFile.sql', 'rb') as f:?
• thclpr: Still with the error :( I tried also to convert to UTF but still getting same error
• Larry Lustig: See this link: bytes.com/topic/oracle/answers/…. You may not be able to submit multiple statements to Oracle in a single execute() call or, if you want to, you may need to add additional statements before and after to create a program block. I suggest reading each line and execute()ing it separately, all within a transaction.

Answer 1

Now it's somewhat clarified - you have a lot of separate SQL statements such as INSERT INTO table (movie,genre) VALUES ('moviename','horror');

Then, you're effectively after cur.executescript() than the current state (I have no idea if pyodbc supports that part of the DB API, but any reason, you can't just execute an execute to the database itself?

• thclpr: i belive pyodbc only supports execute and executemany. i will perform some tests using cx_oracle to see if i can work this out.

Answer 2

When you read a file using read() function, the end line (\n) at the end of file is read too. I think you should use db_call(cfgFile,statements[:-1]) to eliminate the end line.

• Sassan: there may be two endline characters at the end of your file, try this one: db_call(cfgFile,statements.strip())