Saving MySQL query results to a CSV file

Question

I have two queries that I'd like to save together in a single CSV file. I can do this individually but I can't figure out how to copy the two files into a single file (not merge, just copy columns from one file to the other) so perhaps I can do this at the source with the SQL queries.

import MySQLdb, csv
dbServer = 'localhost'
dbUser = 'root'
dbPass = 'password'
dbSchema = 'Name_of_Database'
db = MySQLdb.connect(host=dbServer, user=dbUser,passwd=dbPass,db=dbSchema)
cursor = db.cursor()
cursor.execute("""SELECT * FROM temperature_tbl WHERE Mac_Address = %s AND Date =   CURDATE() ORDER BY Time asc;""",(mac))
first_set = cursor.fetchall()

cursor.execute("""SELECT row1, row2, row3, row4 FROM temperature_tbl WHERE Mac_Address = "xx xx xx xx xx xx" AND Date = CURDATE() ORDER BY Time asc;""")
second_set = cursor.fetchall()

Now that I have the tuple results from these two queries is it possible to write them all at the same time within a CSV File? For instance:

csv_file = open('filename', 'w')
writer = csv.writer(csv_file)
writer.writerow(['col1', 'col2', 'col3', 'col4', 'col5', etc etc etc])
writer.writerows(first_set + second_set)
csv_file.close()

As long as the results of the two queries matches the row header this would be perfect.

EDIT1: Ok how about this. How would I join two tuple of tuples together so that I am not extending the length of the tuples, but joining them in parallel? If I just add tuple1 + tuple2 it extends the length of the list but I want them side by side. EG:

Tuple1:    Tuple2:
x,x,x,x    y,y,y,y
x,x,x,x    y,y,y,y
x,x,x,x    y,y,y,y

If I do tuple1 + tuple2 I get:

x,x,x,x
x,x,x,x
x,x,x,x
y,y,y,y
y,y,y,y
y,y,y,y

What I really want is:

x,x,x,x,y,y,y,y
x,x,x,x,y,y,y,y
x,x,x,x,y,y,y,y

Then I can writerows() and modify the CSV by removing extra columns (for instance Date is referenced twice).

Is this possible?

EDIT2 - SOLUTION

Here is what I did. I made several SQL queries and then list.appended each query result into a large list. This extended horizontally like I wanted it to. Then I was able to writerows() to get a complete CSV file the way I wanted it.

Each query will have ~ 2000 rows and around 9 columns each. Ideally I'd like to add them in parallel so all columns would be expressed at the top and then the actual results would start on the first row below the header and then extend down. EG: If first_set returns columns with data, a,b,c,d,e and second_set returns f,g,h,i,j then the file would look like this: a,b,d,e,f,g,h,i,j and then would have the corresponding rows beneath. — Thor Peterson, Jan 29 '14 at 18:10

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

You basically want to add columns to a CSV file, so take a look here: How to add a new column to a CSV file using Python?

edited May 23 '17 at 12:14

Community

1
1

answered Jan 29 '14 at 07:07

oz123

27,559
27
125
187

Yes I've read that but it doesn't for for two reasons: 1, it assumes to append which I don't want to do, or at least, I need to append several rows, not just one. 2, I am using SQL queries with several columns in the result so I need a slick way of inserting several columns at once. – Thor Peterson Jan 29 '14 at 08:13

score 0 · Answer 2 · answered Jan 29 '14 at 21:48

0

EDIT2 - SOLUTION

Here is what I did. I made several SQL queries and then list.appended each query result into a large list. This extended horizontally like I wanted it to. Then I was able to writerows() to get a complete CSV file the way I wanted it.

answered Jan 29 '14 at 21:48

Thor Peterson

131
1
13

Saving MySQL query results to a CSV file

2 Answers2