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','database':'Nov14','user':'postgres'}
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
Danny OsborneSenior IT and Technical Specialist, ConsultancyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BradyPrincipal Data EngineerCommented:
Firstly your loop is over-writing the sql variable so by the time you run the query you will only have the very last line from the text file. Can you post an example of the text file?  Just post one line of it.

also post an example of what the row looks like in the DB
0
gelonidaCommented:
There's some issues with that code (like SQL code injection, . . . https://xkcd.com/327/ )
If you know that the data file, that you are reading is not created be a potentially malicious person you can ignore that for the time being.

Without diving in your code I see already one obvious problem.

you read only one line and the following loop is then just iterating over the same read line.
You might try following untested modifications:


import psycopg2
params = {'host':'localhost','database':'Nov14','user':'postgres'}
connection = psycopg2.connect(**params)
cursor = connection.cursor()
f = open('C:\Feb 14\clients.txt')

for line in f:
    name = line.strip() # remove end of line character and white space at beginning / end of line
    sql = 'select * from clients where name = "%s"' % name
    cursor.execute(sql)
    result = cursor.fetchall()
    print result
f.close()

Open in new window

0
gelonidaCommented:
They way to avoid sql injection should to change the lines:
sql = 'select * from clients where name = "%s"' % name
cursor.execute(sql)

Open in new window

to
sql = 'select * from clients where name = %s'
cursor.execute(sql, (name, ))

Open in new window


Please refer to:
http://pythonhosted.org//psycopg2/usage.html#passing-parameters-to-sql-queries

Pls note, that my above example is untested
for more info of how to pass parameters to queries
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Danny OsborneSenior IT and Technical Specialist, ConsultancyAuthor Commented:
Thanks Guys, the tip on injection whilst not being currently relevant is definitely needed for the future!

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
0
gelonidaCommented:
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.
0
Danny OsborneSenior IT and Technical Specialist, ConsultancyAuthor Commented:
Yes thanks I will set it up as you suggest, Again, thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.