Danny Osborne
asked on
python - read lines from text file into sql query
Hi,
i'm a newbie to python and am having a problem with a pretty trivial task. I'm trying to take a row at a time from a text file and insert that row value into the while block of a sql query, run the query print it out then move to the next line, insert that value into the while block of the same query and run it/print it again etc... until i've reached the last row of the text file. I've tried the following but there's something wrong. I'm getting null [] back.
The sql is ok and the variable 'line' is being used in the query correctly so it seems to be the python part i'm messing up on.
import psycopg2
params = {'host':'localhost','datab
connection = psycopg2.connect(**params)
cursor = connection.cursor()
f = open('C:\Feb 14\clients.txt')
line = f.readline()
while line:
sql = 'select * from clients where name = ' + "'" + line + "'"
cursor.execute(sql)
result = cursor.fetchall()
print result
line = f.readline()
f.close()
Any ideas?
Danny
i'm a newbie to python and am having a problem with a pretty trivial task. I'm trying to take a row at a time from a text file and insert that row value into the while block of a sql query, run the query print it out then move to the next line, insert that value into the while block of the same query and run it/print it again etc... until i've reached the last row of the text file. I've tried the following but there's something wrong. I'm getting null [] back.
The sql is ok and the variable 'line' is being used in the query correctly so it seems to be the python part i'm messing up on.
import psycopg2
params = {'host':'localhost','datab
connection = psycopg2.connect(**params)
cursor = connection.cursor()
f = open('C:\Feb 14\clients.txt')
line = f.readline()
while line:
sql = 'select * from clients where name = ' + "'" + line + "'"
cursor.execute(sql)
result = cursor.fetchall()
print result
line = f.readline()
f.close()
Any ideas?
Danny
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes it might be, that postgres SQL insists on single quotes and doesn't handle double quotes.
In any case the better way is to use the modified version of my last response, which avoids this issue and SQL injection at the same time.
In any case the better way is to use the modified version of my last response, which avoids this issue and SQL injection at the same time.
ASKER
Yes thanks I will set it up as you suggest, Again, thanks for the help!
ASKER
The script you provided just a needed minor change in one line
From
sql = 'select * from clients where name = "%s"' % name
To
sql = 'select * from clients where name = ' + "'" + name +"'"
When I used the line you constructed it wrapped the variable 'name' in speech marks rather than apostrophes. SQL didn't seem to like that. Is it because i'm using PostgreSQL?
Danny