use Python to read txt file with SQL code in it and execute that code

Asked Sep 18 '15 at 20:38

Active Sep 18 '15 at 20:45

Viewed 1,748 times

I have a series of SQL scripts. I want to use Python to read each of those files and execute that code against Netezza. Here's what I've got so far, but it doesn't seem to actually do anything.

My SQL script (this table starts with plenty data in it, just want to test if I can truncate it for now. The actual code I plan to run will continue by creating new tables and executing other SQL):

/* DROP THE OLD BACKUP */ 
TRUNCATE TABLE DB_LAB..MY_TEST
;

My Python code:

import pyodbc

cnxn = pyodbc.connect('DSN=DB_LAB')
cursor = cnxn.cursor()

sql_example = "F:\\My Documents\\test_sql.txt"

infile = open(sql_example ,'r')

lines = infile.readlines()

print lines

>>>['/* DROP THE OLD BACKUP */ \n',
 'TRUNCATE TABLE DB_LAB..MY_TEST\n',
 ';\n']


sql = (' '.join(lines))

cnxn.execute(sql)
cnxn.close()

After I execute this code I get no errors but MY_TEST hasn't been truncated.

edited Sep 18 '15 at 20:45

asked Sep 18 '15 at 20:38

ADJ

4,892
10
50
83

pardon my ignorance (I am not an SQL guru): is the syntax of the command `TRUNCATE TABLE DB_LAB..MY_TEST` correct? – Pynchia Sep 18 '15 at 20:43
At least on Netezza, yes. – ADJ Sep 18 '15 at 20:44
what is the meaning of the two dots? – Pynchia Sep 18 '15 at 20:48
1

would [this](http://www.forumtopics.com/busobj/viewtopic.php?p=936521) help? – Pynchia Sep 18 '15 at 20:50
1

Can you also do `cnxn.commit()` just before `cnxn.close()` and see if that works? – mechanical_meat Sep 18 '15 at 21:26
Maybe it doesn't like the embedded end of lines. – Robert E Sep 19 '15 at 00:46
Thanks bernie, the cnxn.commit() worked like a charm! – ADJ Sep 22 '15 at 16:16

use Python to read txt file with SQL code in it and execute that code

0 Answers0