Python extract mysql to csv

Question

I trying to extract data from mysql to a csv-file. The problem is the rows dont comes i same order as in mysql.

In mysql I have this in uid table 1,2,3,4,5,6,7,8,9,10,11 but the response is 10,9,11,2,1,4,3,6,5,8,7.

cursor=conn.cursor()
cursor.execute("SELECT * FROM test ORDER BY uid;")
rows = cursor.fetchall()

#rows = sorted(rows1)
allResults = {}
for row in rows:
#for row in rows:
    uid = str(row[0])
    data = str(row[1])

    allResults[uid] = [uid,data]

    #numbers.append(allResults)


return allResults


def getCSV():

    if request.method == 'POST':
        nr = str(request.form['nrsearch']).strip()
        try = str(request.form['trysearch']).strip()
        year = str(request.form['yearsearch']).strip()

        allResults = readFromDBCSV(nr, try, year)
        #print(allResults)

        filename = str(nr)+"."+str(try)+"."+str(year)+'.csv'

        if len(allResults) > 0:
            with open('static/CSV/'+str(filename), 'wb') as csvfile:
                CSVwriter = csv.writer(csvfile, delimiter=';', quotechar='|', quoting=csv.QUOTE_MINIMAL)
                CSVwriter.writerow(['test'])
                for result in allResults:
                    CSVwriter.writerow(allResults[result])
            return '''DONE '''+str(len(allResults))+''' RESULTS<br>
    <form method=post enctype=multipart/form-data action="/doneCSV/">
    <input type="hidden" name="filename" value="'''+str(filename)+'''">
    <input type=submit name="Download CSV" value="Download CSV"><br>
    <a href="/getCSV/">Return</a>'''
    else:   
    return 'NO RESULTS<br><a href="/getCSV/">Return</a>'

    return render_template('searchCSV.html')

Thanks for your kindnes. I have try all of your tips with no luck:( I`m newbee in python:(:( I have change the code to this: cursor=conn.cursor() cursor.execute("SELECT * FROM test2 ORDER BY uid;") for row in cursor: uid = str(row[0]) data = str(row[1]) allResults[uid]= [uid,data] print(allResults[uid]) return allResults Why is the print(allResults[uid]) givs the right order?(1,2,3...). — mattias77
– mattias77, Commented Jun 13, 2018 at 5:16

bruno desthuilliers · Accepted Answer · 2018-06-13 11:50:28Z

0

Python dicts are unordered - this is documented FWIW. If you want to keep insertion order, use a collection.OrderedDict instead of a dict.

Now for your use case, the solution is much simpler: don't use a dict at all, either use the list returned by cursor.fetchall() or, even better, just pass your cursor to the writer (a cursor is an iterable).

cursor.execute(your_query_here)
with open(path/to/file.csv, "wb") as f:
    writer = csv.writer(f, ....)
    writer.writerows(cursor)

edited Jun 13, 2018 at 11:50

answered Jun 13, 2018 at 11:45

bruno desthuilliers

78.3k6 gold badges102 silver badges129 bronze badges

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

2 Comments

mattias77 Over a year ago

:)) Thanks it`s working:) . I have try in 3 days with no luck. thanks again

bruno desthuilliers Over a year ago

Glad I could help - and feel free to accept the answer if it solved your problem ;)

Narendra Prasath · Accepted Answer · 2018-06-14 04:51:22Z

-1

In your case: you are storing into dictionary variable. usually dictionary won't preserve the order you are inserting. If you want a order, then use OrderedDict()

from collections import OrderedDict
allResults   = OrderedDict()

My Other suggestion would be: using pandas functions you can easily save into CSV

check the read_sql function http://pandas.pydata.org/pandas-docs/version/0.22/io.html#sql-queries

edited Jun 14, 2018 at 4:51

answered Jun 12, 2018 at 6:24

Narendra Prasath

1,5311 gold badge10 silver badges20 bronze badges

2 Comments

bruno desthuilliers Over a year ago

Could you answer the OP's question instead ? The question is "The problem is the rows dont comes i same order as in mysql."

Narendra Prasath Over a year ago

please check the answer i given now @brunodesthuilliers

Collectives™ on Stack Overflow

Python extract mysql to csv

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related