MySQL query with Python not returning all results

Question

I have written some python code to connect to a MySQL database, open a text file and, for each entry in the text file, execute a query and write the result to an output file. Rather than writing the result of every query to the output file, however only a single result is being written. If the query I'm using has no arguments it all works fine. The issue only arises when I try to add in arguments.

I'm fairly new to Python so I may be making a silly mistake but I haven't yet been able to come across anything that's helped so any assistance would be greatly appreciated.

The code I have is:

output = open("results.txt", "w+")

cnx= mysql.connector.connect(**config)  
cursor = cnx.cursor()                       

with open("input.txt") as i:
    for line in i:                                  

        # Construct query to get type
        query = ("select type from table1, table2 " 
        "where table1.name = %s and table1.id = table2.id")

        # Query arguments
        args = (line)

        cursor.execute(query, args)         # Execute query

        for entry in cursor:                
            output.write(str(entry) + "\n")

cursor.close()                                      
cnx.close()

Did you close the file using output.close()? If not, maybe the data is lost in the output buffer. — dnet
– dnet, Commented Jul 8, 2013 at 12:44
For the record: "args = (line)" should be "args = (line,)", but see answer of joente for real fix. — geertjanvdk
– geertjanvdk, Commented Jul 9, 2013 at 9:26

joente · Accepted Answer · 2013-07-08 11:47:02Z

1

I'm not sure about the query you are using, but I think if your query is working, this should be close to what you want:

output = open('myoutputfile.txt', 'w')
cnx = mysql.connector.connect(**config)  
cursor = cnx.cursor()                       

# Construct query to get type, there's no need to include this in the loop
query = ("""SELECT type FROM table1, table2 
    WHERE table1.name = %s AND table1.id = table2.id""")

with open("input.txt") as f:
    for line in f:                                  

        # Query arguments
        args = (line.strip(), ) # added .strip()

        cursor.execute(query, args)       # Exec query

        entries = cursor.fetchall()
        for entry in entries:                
            output.write(str(entry[0]) + "\n") 

cnx.close()
output.close()

edited Jul 8, 2013 at 11:47

answered Jul 8, 2013 at 10:07

joente

8567 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Riddle Over a year ago

That's not working for me either unfortunately. I'm getting the same result as what I had originally. I've tested the query manually so I know its definitely working. It works if the query has no arguments but once they're added in it just gives the single result.

joente Over a year ago

I've change a little bit to the code. (added .strip() to the line argument). Maybe that solves your problem because I guess the end-line character is included otherwise...

joente Over a year ago

no problem:-) if you think my answer is useful, could you vote on it?

Pawel Miech · Accepted Answer · 2013-07-08 09:54:27Z

You are not opening output file for writing, at least not in the piece of code you've published.

        for entry in cursor:  
            ## what output? when and how was it opened?            
            output.write(str(entry) + "\n")

Have you opened "output.txt" with "w" (which stands for write) mode earlier? In this code you're only opening "input.txt" file on line 4. If you want to write to "input.txt" file in append mode the code needs to be:

i.write(str(entry) + "\n")

(and the file must be opened in append or write mode). Also in query arguments you don't need to pass four lines. In your query you only supply one parameter (table1.name = %s) you only need to pass parameter to this %s, which I guess will be line.

I have opened the output file before opening the connection. Should have included that sorry but I've updated the code that I posted now. Some stuff does get written to the output file just not all of it is there. As for having too many parameters, that was just a mistake in what I posted. I have it right in my code and fixed it here, sorry about that.

Collectives™ on Stack Overflow

MySQL query with Python not returning all results

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related