It is easy to execute SQL with Python and output the result in Excel

I recently started studying Python I thought it would be easier to execute SQL in Python and output the result in Excel, so I summarized it.

It's about 50 lines of code.

things to do

  1. Execute SQL in SQL folder in order with Python
  2. Excel output SQL execution result with the same file name as SQL
  3. Zip Excel
  4. Put it on the file server

Tried environment


--Save the SQL you want to execute in a folder with the name ~ .sql --Import some Python libraries

Source / Run

import mysql.connector
import datetime
import glob
import os
import pandas as pd
import csv
import pyminizip
import pathlib
import shutil

def exec():

    # 1.SQL execution in python------------------------------------

    #DB connection
    conn = mysql.connector.connect(
       host = 'host',
       port = 'port',
       user = 'User',
       password = 'password',
       database = 'Database' 
    #Get SQL file
    os.chdir("SQL folder")
    sql_file_list = glob.glob("*.sql")
    #Create folder
    now ='%Y%m%d%H%M%S')

    #SQL execution
    for sql_file in sql_file_list:


        with open(sql_file, 'r') as f:
            sql_query =
        df = pd.read_sql_query(sql_query, con=conn) 

     # 2.Output the result in Excel------------------------------------

        #For excel output
        df.to_excel(now + "\\" + sql_file.replace('.sql', '.xlsx'), sheet_name=sql_file.replace('.sql', ''), index=False)
        #For CSV output
        df.to_csv(now + "\\" + sql_file.replace('.sql', '.csv'), encoding="utf-8_sig", quoting=csv.QUOTE_NONNUMERIC, index=False)

    #If you have individual edits, do it here

    #Get result file
    result_file_list = glob.glob("*.xlsx")    

    # 3.ZIP------------------------------------

    #Result file compression(Japanese file name not supported)
    file_path = []
    for i in range(len(result_file_list)):
    pyminizip.compress_multiple(result_file_list,file_path, now + '.zip','pass',0)
    # 4.Put on file server------------------------------------

    #Result file upload
    share = pathlib.WindowsPath(r'file server' + now + '.zip')
    shutil.copyfile(now + '.zip', share)


Recommended Posts

It is easy to execute SQL with Python and output the result in Excel
Recursively get the Excel list in a specific folder with python and write it to Excel.
Execute raw SQL using python data source with redash and display the result
How to input a character string in Python and output it as it is or in the opposite direction.
[Mac] A super-easy way to execute system commands in Python and output the results
An easy way to view the time taken in Python and a smarter way to improve it
Output the contents of ~ .xlsx in the folder to HTML with Python
Convert the result of python optparse to dict and utilize it
How to use is and == in Python
[Python] Sweet Is it sweet? About suites and expressions in the official documentation
Extract the TOP command result with USER and output it as CSV
Easy server monitoring with AWS Lambda (Python) and result notification in Slack
I set the environment variable with Docker and displayed it in Python
Read CSV file with Python and convert it to DataFrame as it is
About the difference between "==" and "is" in python
python Binary search It is surprisingly easy to implement bisect.bisect_left and bisect.bisect_right from 0
[Python] How to name table data and output it in csv (to_csv method)
Use libsixel to output Sixel in Python and output a Matplotlib graph to the terminal.
Prepare a development environment that is portable and easy to duplicate without polluting the environment with Python embeddable (Windows)
(Diary 1) How to create, reference, and register data in the SQL database of Microsoft Azure service with python
An engineer who has noticed the emo of cryptography is trying to implement it in Python and defeat it
Procedure to load MNIST with python and output to png
Convert the image in .zip to PDF with Python
Get the result in dict format with Python psycopg2
Read json file with Python, format it, and output json
[Python] How to output the list values in order
Run the output code with tkinter, saying "A, pretending to be B" in python
The result of making a map album of Italy honeymoon in Python and sharing it
Make it easy to install the ROS2 development environment with pip install on Python venv
[Note] How to write QR code and description in the same image with python
Output the specified table of Oracle database in Python to Excel for each file
Easy to use Nifty Cloud API with botocore and python
Try to make it using GUI and PyQt in Python
After all it is wrong to cat with python subprocess.
Output "Draw ferns programmatically" to the drawing process in Python
Change the standard output destination to a file in Python
[pyqtgraph] Add region to the graph and link it with the graph region
Sort and output the elements in the list as elements and multiples in Python.
Tips for coding short and easy to read in Python
Easy with just Python! Output Graphviz figures in format!
Object-oriented in C: Refactored "○ ✕ game" and ported it to Python
How to execute external shell scripts and commands in python
Install CaboCha in Ubuntu environment and call it with Python.
How to log in to AtCoder with Python and submit automatically
I made a class to get the analysis result by MeCab in ndarray with python
[Super easy! ] How to display the contents of dictionaries and lists including Japanese in Python
It was great to edit the Python file in the Raspberry Pi with Atom's remote function
Return the image data with Flask of Python and draw it to the canvas element of HTML
I also tried to imitate the function monad and State monad with a generator in Python
If you want to put an argument in the closure function and execute it later
[Python] What is a tuple? Explains how to use without tuples and how to use it with examples.
Put Ubuntu in Raspi, put Docker on it, and control GPIO with python from the container
[VLC] How to deal with the problem that it is not in the foreground during playback
An easy way to hit the Amazon Product API in Python
How is the progress? Let's get on with the boom ?? in Python
processing to use notMNIST data in Python (and tried to classify it)
In the python dictionary, if a non-existent key is accessed, initialize it with an arbitrary value
How to install OpenCV on Cloud9 and run it in Python
Difference between == and is in python
Easy way to round off to the nearest whole number with python3
I want to solve APG4b with Python (only 4.01 and 4.04 in Chapter 4)