DEV Community

loading...

Convert Microsoft SQL Server Database Tables to CSV

israjurrahman profile image Israjur Rahman ・2 min read

We can convert and save Microsoft SQL Server database tables to CSV using python. I've recently written a python script to automate a process to convert Microsoft SQL Server database tables to CSV format and save it to drive so that I can use it later for other purposes. I've used pyodbc and pandas library to automate this process.

Firstly we have to establish a connection to Microsoft SQL Server database using pyodbc library.

import pyodbc 

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=ISRAJ\SQLEXPRESS;'
                      'Database=TEST_DB;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
Enter fullscreen mode Exit fullscreen mode

Here we have provided server name, database name and if it has any username and password we have to add the credentials too.

After that we will query table names so that we can see the tables we have and can select the table name we want to convert to CSV.

sql = "SELECT TABLE_NAME FROM TEST_DB.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"

cursor.execute(sql)
Enter fullscreen mode Exit fullscreen mode

We have written a query to select all the tables from database and by using cursor.execute we are executing the query.

Now we can loop through the table names:

for (table_name,) in cursor:
    print(table_name)
Enter fullscreen mode Exit fullscreen mode

Output will be something like this:

Table_One
Table_Eight
Table_Two
Table_Three
Table_Four
Table_Five
Table_Six
Table_Seven
Table_Nine
Enter fullscreen mode Exit fullscreen mode

Now we will use pandas to get the specific table data to dataframe.

import pandas.io.sql as psql

sql = "SELECT * FROM TEST_DB.dbo.Table_One"
df = psql.read_sql(sql, conn)
Enter fullscreen mode Exit fullscreen mode

Here we have written another query to select all from Table_One and psql.read_sql will execute the query and convert the data into dataframe.

Now we will use pandas to_csv method to save the dataframe as csv.

df.to_csv("table_one_in_csv.csv", index=False)
Enter fullscreen mode Exit fullscreen mode

Thanks For Reading!

If you want to test or get the code here I’ve added a simple command line tool to convert Microsoft SQL Server database tables to CSV.

https://github.com/IsrajurRahman/mssql2csv

Discussion (0)

pic
Editor guide