?
Solved

python - read lines from text file into sql query

Posted on 2014-12-11
6
Medium Priority
?
1,304 Views
Last Modified: 2014-12-11
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
0
Comment
Question by:DannyJOsborne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 20

Assisted Solution

by:Mark Brady
Mark Brady earned 400 total points
ID: 40493603
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
 
LVL 17

Assisted Solution

by:gelonida
gelonida earned 1600 total points
ID: 40493613
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
 
LVL 17

Accepted Solution

by:
gelonida earned 1600 total points
ID: 40493621
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Closing Comment

by:DannyJOsborne
ID: 40493666
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
 
LVL 17

Expert Comment

by:gelonida
ID: 40493678
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
 

Author Comment

by:DannyJOsborne
ID: 40493679
Yes thanks I will set it up as you suggest, Again, thanks for the help!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

"The time has come," the Walrus said, "To talk of many things: Of sets--and lists--and dictionaries-- Of variable kinks-- And why you see it changing not-- And why so strange are strings." This part describes how variables and references (see …
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question