How to save results from mySQL database into a file or variable in python?

Question

I have a little app which takes users coordinates from client and searches for a nearest location in my mySQL database. I need to grab the results from my database and save them to a file or assign these results to a variable. What do I need to add to a code below to achieve it?

import mysql.connector


mycursor = mydb.cursor()

mycursor.execute("SELECT dbx, dby, SQRT(
    POW(69.1 * (dbx - userx), 2) +
    POW(69.1 * (usery - dby) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 125 ORDER BY distance;")

mydb.commit()

dbx and dby are longitude and latitude parameters from the points which are stored in database userx and usery are longitude and latitude parameters taken from the client-side.

How to save results from mySQL database into a file Execute SELECT .. INTO OUTFILE. — Akina
– Akina, Commented Feb 10, 2021 at 13:23

Mayank Patel · Accepted Answer · 2021-02-10 13:33:44Z

2

The query results can be placed into the file /tmp/some_filename.txt using the query:

SELECT dbx, dby, SQRT(
    POW(69.1 * (dbx - userx), 2) +
    POW(69.1 * (usery - dby) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 125 ORDER BY distance
INTO OUTFILE '/tmp/some_filename.txt'

If you want to use a separator and quote character, add this:

FIELDS TERMINATED BY ','
ENCLOSED BY '"'

If you want to add line terminator for output file:

LINES TERMINATED BY '\n'

answered Feb 10, 2021 at 13:33

Mayank Patel

1,5811 gold badge14 silver badges20 bronze badges

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

Comments

IoaTzimas · Accepted Answer · 2021-02-10 13:38:36Z

2

You can fetch the result of your query and save it to a variable, with the following:

res=mycursor.execute("SELECT dbx, dby, SQRT(
    POW(69.1 * (dbx - userx), 2) +
    POW(69.1 * (usery - dby) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 125 ORDER BY distance;").fetcall()

res will be a list of tuples eg [(0.53, 75.32, 2000), (0.55, 62.75, 3000)]

You can now save it to txt file with the following:

with open('res.txt', 'w') as f:
    f.writelines([(str(i)+'\n') for i in res])

You can also save it as csv, as json, or any other format you want, but txt in your case will be fine

edited Feb 10, 2021 at 13:38

answered Feb 10, 2021 at 13:30

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to save results from mySQL database into a file or variable in python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related