CODEWITHSUNDEEP
pythonpyodbc
thclpr
thclpr

Reputation: 5938

Insert data from file into database

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.

Upvotes: 1

Views: 1580

Answers (2)

Jon Clements
Jon Clements

Reputation: 142176

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?

Upvotes: 1

Sassan
Sassan

Reputation: 2339

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.

Upvotes: 0

Related Questions