How to save the results of select query in MySQL on Ubuntu through pyodbc into a local file using python is shown in this blog.
Contents
▼1. Save data of MySQL on Ubuntu into a file
In the past, how to save data My MySQL into a file using Java was provided in this blog as below. in this case, it will be implemented by Python.
Java -Save the results of select for MySQL into a file No.85
▼2. Prerequisites
How to create an environment to run python code for MySQL is shown in this blog.
Python – Connect to MySQL on Ubuntu 20.04 by pyodbc No.98
▼3. Python code to save results of select query for MySQL into a file
3-1. Creating python code
Python code “runmysqlquery.py” is created to save results of select statement for MySQL into a file. according to your environment, parameters should be changed.
import pyodbc
driver = '{MYSQL ODBC 8.0 Driver}'
server = 'localhost'
dbname = 'testdb'
user = 'hiveuser'
pw = 'hivepassword'
query = "select * from testtbl"
filepath = "/home/xxx/results.txt"
try:
conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+dbname+';UID='+user+';PWD='+pw+';')
cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
while row:
print(str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()
# save results of a select query to file
with open(filepath, 'w') as f:
cursor.execute(query)
row = cursor.fetchone()
while row:
f.write(str(row[0]) + " " + str(row[1]) + "\n")
row = cursor.fetchone()
except pyodbc.Error as ex:
sqlstate = ex.args[1]
print(sqlstate)
3-2. Results of this code
“results.txt” file is created by this code
python3 runmysqlquery.py
1 1
2 2
3 3cat results.txt
1 1
2 2
3 3▼4. Reference
- Java -Save the results of select for MySQL into a file No.85
- Python – Connect to MySQL on Ubuntu 20.04 by pyodbc No.98
- Python in Visual Studio Code
That’s all. Have a nice day ahead !!!