How to copy entire SQL Server table into CSV including column headers?

Question

Summary

I have a Python program (2.7) that connects to a SQL Server database using SQLAlchemy. I want to copy the entire SQL table into a local CSV file (including column headers). I'm new to SQLAlchemy (version .7) and so far I'm able to dump the entire csv file, but I have to explicitly list my column headers.

Question

How do I copy an entire SQL table into a local CSV file (including column headers)? I don't want to explicitly type in my column headers. The reason is that I want to avoid changing the code if there's changes in the table's columns.

Code

import sqlalchemy

# Setup connection info, assume database connection info is correct
SQLALCHEMY_CONNECTION = (DB_DRIVER_SQLALCHEMY + '://'
    + DB_UID + ":" + DB_PWD + "@" + DB_SERVER + "/" + DB_DATABASE 
    )
engine = sqlalchemy.create_engine(SQLALCHEMY_CONNECTION, echo=True)
metadata = sqlalchemy.MetaData(bind=engine)
vw_AllCenterChatOverview = sqlalchemy.Table( \
    'vw_AllCenterChatOverview', metadata, autoload=True)
metadata.create_all(engine)
conn = engine.connect()

# Run the SQL Select Statement
result = conn.execute("""SELECT * FROM 
        [LifelineChatDB].[dbo].[vw_AllCenterChatOverview]""")

# Open file 'output.csv' and write SQL query contents to it
f = csv.writer(open('output.csv', 'wb'))
f.writerow(['StartTime', 'EndTime', 'Type', 'ChatName', 'Queue', 'Account',\
    'Operator', 'Accepted', 'WaitTimeSeconds', 'PreChatSurveySkipped',\
    'TotalTimeInQ', 'CrisisCenterKey']) # Where I explicitly list table headers
for row in result:
    try:
         f.writerow(row)
    except UnicodeError:
         print "Error running this line ", row
result.close()

Table Structure

In my example, 'vw_AllCenterChatOverview' is the table. Here's the Table Headers:

StartTime, EndTime, Type, ChatName, Queue, Account, Operator, Accepted, WaitTimeSeconds, PreChatSurveySkipped, TotalTimeInQ, CrisisCenterKey

Thanks in advance!

You can definitely query the database for a list of column names, and then write the result as your header row in the csv. Take a look at stuff like [this](http://stackoverflow.com/questions/6455560/how-to-get-column-names-from-sqlalchemy-result-declarative-syntax) — Samvel, Mar 07 '14 at 15:55

score 3 · Accepted Answer · answered Mar 07 '14 at 16:47

3

Use ResultProxy.keys:

# Run the SQL Select Statement
result = conn.execute("""SELECT * FROM 
        [LifelineChatDB].[dbo].[vw_AllCenterChatOverview]""")

# Get column names
column_names = result.keys()

answered Mar 07 '14 at 16:47

van

74,297
13
168
171

Much appreciated! I replaced the explicit f.writerow containing the header names with f.writerow(result.keys()) and it automatically wrote the column headers. Thanks. – Will Mar 07 '14 at 17:16

How to copy entire SQL Server table into CSV including column headers?

1 Answers1