Solved

python - read lines from text file into sql query

Posted on 2014-12-11
6
1,170 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 17

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 17

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
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Variable is a place holder or reserved memory locations to store any value. Which means whenever we create a variable, indirectly we are reserving some space in the memory. The interpreter assigns or allocates some space in the memory based on the d…
Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
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…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

691 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