Solved

python - read lines from text file into sql query

Posted on 2014-12-11
6
1,025 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 100 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 16

Assisted Solution

by:gelonida
gelonida earned 400 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 16

Accepted Solution

by:
gelonida earned 400 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 16

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Dictionaries contain key:value pairs. Which means a collection of tuples with an attribute name and an assigned value to it. The semicolon present in between each key and values and attribute with values are delimited with a comma.  In python we can…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
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…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…

749 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