Solved

python - read lines from text file into sql query

Posted on 2014-12-11
6
1,080 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

"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 …
Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
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.

738 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