Python sql query to csv

SQL to CSV via Python, with Headers

Nifty Python code that converts a SQL database file into a CSV file and includes column headers.

There are quite a few Python scripts available that will convert a SQL database file into a CSV, but I have found the below code to be quite good. It is based on Till Hoffman’s code with modifications to extract column names.

Column names are encoded in the cursor.description parameter, but this actually contains a list of tuples, of which column_name is the first. Therefore a colnames parameter is created to allow just the column name to be added to the output as a column header in the CSV file.

The entire code is as follows:

import sqlite3 import pandas as pd # Open the file f = open('/path/to/output.csv', 'w') # Create a connection and get a cursor connection = sqlite3.connect('/path/to/database.sql') cursor = connection.cursor() # Execute the query cursor.execute('select * from mydata') # Get Header Names (without tuples) colnames = [desc[0] for desc in cursor.description] # Get data in batches while True: # Read the data df = pd.DataFrame(cursor.fetchall()) # We are done if there are no data if len(df) == 0: break # Let us write to the file else: df.to_csv(f, header=colnames) # Clean up f.close() cursor.close() connection.close()

Rather than hard-coding commands and variables into the script, it’s better to parse them as variables via the command line. To do this, I utilised the argparse utility and modified the script as follows:

import sqlite3 import pandas as pd import argparse def options(): parser = argparse.ArgumentParser(description="Convert a SQL file to a CSV file") parser.add_argument("-i", "--input", help="Input SQL file.", required=True) parser.add_argument("-o", "--output", help="Output CSV file.", required=False) parser.add_argument("-c","--command", help="SQL command", required=True) args = parser.parse_args() return args def main(): # Get options args = options() # Open the file f = open(args.output, 'w') # Create a connection and get a cursor connection = sqlite3.connect(args.input) cursor = connection.cursor() # Execute the query cursor.execute(args.command) # Get Header Names (without tuples) colnames = [desc[0] for desc in cursor.description] # Get data in batches while True: # Read the data df = pd.DataFrame(cursor.fetchall()) # We are done if there are no data if len(df) == 0: break # Let us write to the file else: df.to_csv(f, header=colnames) # Clean up f.close() cursor.close() connection.close() if __name__ == '__main__': main()

Usage on the command line is as follows:

$ python /path/to/script.py -i "/path/to/database.sql" -o "/path/to/output.csv" -c "select * from mydata"

The -c flag can be used to run advanced SQL commands such as a LEFT JOIN, if desired but must have a basic SQL query at a minimum. Note that the SQL commands must be wrapped in double quotes.

Comments

One response to “SQL to CSV via Python, with Headers”

Источник

How to Export SQL Server Table to CSV using Python

In this guide, you’ll see the complete steps to export SQL Server table to a CSV file using Python.

The Example

Let’s say that you’d like to export the following table (called the ‘dbo.product‘ table) from SQL Server to CSV using Python:

product_id	product_name	price
1	Computer	800
2	TV	1200
3	Printer	150
4	Desk	400
5	Chair	120
6	Tablet	300

Here are the steps that you may follow.

Steps to Export SQL Server Table to CSV using Python

Step 1: Install the Pyodbc Package

If you haven’t already done so, install the pyodbc package using the command below (under Windows):

You may check the following guide for the instructions to install a package in Python using pip.

Step 2: Connect Python to SQL Server

There are several items that you may retrieve before you connect Python to SQL Server, including the:

For example, let’s suppose that we are given the information below:

• The server name is: RON\SQLEXPRESS
• The database name is: test_database

Therefore, the code to connect Python to SQL Server would look as follows (note that you’ll need to adjust the code to reflect your server and database information):

import pyodbc conn = pyodbc.connect('Driver=;' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;')

You may review the following guide for the complete steps to connect Python to SQL Server.

Step 3: Export the SQL Server Table to CSV using Python

For the final step, you may use the Pandas package to export the table from SQL Server to CSV.

• To install the Pandas package if you haven’t already done so. You can install the Pandas package using this command: pip install pandas
• The query to get the results to be exported. For our example, the query is: select * from test_database.dbo.product
• The path where the CSV file will be saved. For our example, the path is: C:\Users\Ron\Desktop\exported_data.csv

Once you retrieved the above information, you’ll need to add the following syntax into the code:

import pandas as pd sql_query = pd.read_sql_query(''' select * from test_database.dbo.product ''' ,conn) # here, the 'conn' is the variable that contains your database connection information from step 2 df = pd.DataFrame(sql_query) df.to_csv (r'C:\Users\Ron\Desktop\exported_data.csv', index = False) # place 'r' before the path name

Putting all the components together:

import pandas as pd import pyodbc conn = pyodbc.connect('Driver=;' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') sql_query = pd.read_sql_query(''' select * from test_database.dbo.product ''' ,conn) # here, the 'conn' is the variable that contains your database connection information from step 2 df = pd.DataFrame(sql_query) df.to_csv (r'C:\Users\Ron\Desktop\exported_data.csv', index = False) # place 'r' before the path name

Run the code in Python (adjusted to your database connection information and path), and your CSV file will be exported to your specified location.

Once you open the file, you should see this data:

product_id	product_name	price
1	Computer	800
2	TV	1200
3	Printer	150
4	Desk	400
5	Chair	120
6	Tablet	300

You may also want to check the following guide for the steps to import a CSV file into SQL Server using Python.

Источник

How to export a SQL Server table into a CSV file in Python?

In this tutorial, we will learn how to export a SQL Server table into a CSV file using only Python scripts. We will go through 4 main steps. The first one is creating a sample table in SQL Server. The second one is connecting to the SQL instance. And the third one is to retrieve data from the SQL Server table using a basic select statement.

Then we’ll export the content of the table to a CSV file using the csv library in Python. After that we’ll wrap up all the steps into a unique script. By the end of this tutorial, you will know how to export data from a SQL Server database to a CSV file using Python. But first you need a sample SQL table to export.

1. Create a sample MS SQL table to export

This code creates a sample SQL Server table named dbo.employees in a database and inserts four sample rows. The table has four columns: id, name, department, and salary. And the id column is set as the primary key of the table.

The INSERT INTO statements insert four rows of data into the dbo.employees table. Each row has values for each of the columns in the order they were specified, i.e. : id, name, department, and salary.

CREATE TABLE dbo.employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT ); INSERT INTO employees VALUES (1, 'John Smith', 'Sales', 50000); INSERT INTO employees VALUES (2, 'Jane Doe', 'Marketing', 60000); INSERT INTO employees VALUES (3, 'Bob Johnson', 'IT', 70000); INSERT INTO employees VALUES (4, 'Alice Wong', 'HR', 55000);

Once done, technically you need to perform 3 simple steps, using the scripts provided below.

2. Connect to the SQL Server database

Indeed, technically the steps are very similar to the Excel export using Python, but of course the export module used in the next step is different in this case. The first step is to connect to the SQL Server database using the pyodbc module.

In my case i use a SQL Server 2019 installed on a Windows 10 machine. And I use Visual Studio 2019 to develop my Python scripts. Here is an example code that establishes a connection to a SQL Server database. I use a Windows authentication so I do not enter a user and a password.

# import the pyodbc module to manage the odbc conection import pyodbc # declare some variables to store the connections details driver = 'SQL Server' server = 'localhost' database = 'Expert-Only' # connect to the local SQL Server database connection = pyodbc.connect(f'DRIVER=;' f'SERVER=;' f'DATABASE=;' f'Trusted_Connection=yes;')

Make sure to replace the placeholders with the actual values for your SQL Server connection string: driver , server , database , and if need be username and password . In this case the code would be more like this:

import pyodbc # connect to the SQL Server database server = 'localhost' database = 'database_name' username = 'username' password = 'password' cnxn = pyodbc.connect('DRIVER=;' f'SERVER=;' f'DATABASE=;' f'UID=;' f'PWD=')

3. Retrieve data from the SQL Server table

Once the connection to the SQL Server database is established, we can retrieve the data from the table using a T-SQL query. Here is an example code that retrieves all the rows from the dbo.employees table we created in the first step. Make sure to replace employees with the actual name of your SQL Server table, of course.

# retrieve the data from the employees table cursor = connection.cursor() cursor.execute('SELECT * FROM dbo.employees') data = cursor.fetchall()

4. Create the CSV file and export the data into it

Finally, we can create a CSV file using the csv module and write the data to it. Here is an example code that creates a new CSV file named employees.csv and writes the retrieved data to it. Once again, Make sure to replace the path and name to the file with the actual name that you want to give to your CSV file.

# import the Python csv module import csv # create a new CSV file and write data to it with open("C:\data\employees.csv", mode='w', newline='') as file: writer = csv.writer(file) writer.writerows(data)

5. Full Python code example to export SQL Server table in a CSV file

The full Python code example provided below demonstrates all the steps at once. I.e., how to export the SQL Server data into a CSV file using Python. Indeed, the script imports the necessary libraries, i.e. pyodbc and csv and sets up a connection to the database. Then it executes a SQL query to select the data. And then writes the data to a new CSV file using the csv library.

The headers are extracted from the cursor description and written to the file first, followed by the data itself. Once the export is complete, the cursor and the database connection are closed. This code can be easily modified to fit your specific use case by adjusting the connection string, table name, and file name.

import pyodbc import csv # Step 1: Connect to the SQL Server instance # declare some variables to store the connections details driver = 'SQL Server' server = 'localhost' database = 'Expert-Only' # connect to the local SQL Server database connection = pyodbc.connect(f'DRIVER=;' f'SERVER=;' f'DATABASE=;' f'Trusted_Connection=yes;') # Step 2: Retrieve data from the SQL Server table cursor = connection.cursor() cursor.execute('SELECT * FROM dbo.employees') data = cursor.fetchall() # Step 3: Write data to the CSV file with open("C:\data\employees.csv", mode='w', newline='') as file: writer = csv.writer(file) writer.writerow([x[0] for x in cursor.description]) # write header for row in data: writer.writerow(row) # Step 4: Close the database connection cursor.close() connection.close()

The result is a CSV file exported directly in the C:\data\ folder, with the same content as the original MS SQL table.

And if you are not using a Windows account to execute the Python script:

6. Conclusion on exporting SQL Server data to CSV in Python

In conclusion, after following all the steps outlined in this Python tutorial, you should now be able to export a SQL Server table to a CSV file. Remember to first connect to your SQL Server database using pyodbc, then retrieve the data from the table, and finally write the data to a CSV file using the csv module. Exporting data from a SQL Server database to a CSV file can be a useful skill for many data analysis and reporting tasks.

By automating this process with Python, you can save yourself a lot of time and effort compared to doing it manually in Excel or other software. Indeed, Python provides a powerful set of tools for working with databases and data files. By mastering these tools, you can greatly enhance your productivity as a data analyst or scientist.

More Python tutorials about data and file manipulations

Источник