I am new to Python. I've successfully connected to my SQL database via an odbc connection and I am pulling data from a table. How do I then get that data into a Excel workbook. Preferably using the xlsxwriter module.
import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=SQLSERVER;PORT=XX;DATABASE=dbname;UID=sa;PWD=##')
cursor = cnxn.cursor()
cursor.execute("select * from T1 where C2 not like '%text%'")
for row in cursor:
print row.1, row.2, row3
cursor.close()
cnxn.close()
pandas is the easiest bet for this. Here's an example using sqlite as the database backend:
In [38]: import pandas as pd
In [39]: df
Out[39]:
0 1 2 3 4
0 0.092719 0.664147 0.677834 0.605845 0.569223
1 0.656272 0.321661 0.294219 0.818676 0.010610
2 0.041692 0.721683 0.163525 0.175113 0.580234
3 0.852775 0.106076 0.049080 0.649362 0.265763
4 0.481842 0.942276 0.462951 0.386705 0.205302
In [40]: df.to_sql("tbl", sqlite3.connect("test.sqlite"), index=False)
In [41]: new_df = pd.read_sql("select * from tbl", sqlite3.connect("test.sqlite"))
In [42]: new_df
Out[42]:
0 1 2 3 4
0 0.092719 0.664147 0.677834 0.605845 0.569223
1 0.656272 0.321661 0.294219 0.818676 0.010610
2 0.041692 0.721683 0.163525 0.175113 0.580234
3 0.852775 0.106076 0.049080 0.649362 0.265763
4 0.481842 0.942276 0.462951 0.386705 0.205302
In [43]: new_df.to_excel("out.xlsx")
Lines 41 and 43 are the main ones, and this results in a single sheet Excel doc named out.xlsx in the current folder.
Since you already have your data, it seems like your question is simply how to get your data into Excel. Let's say you have data in an object named rows, which is a list of result rows.
import xlsxwriter
workbook = xlsxwriter.Workbook('YourResults.xlsx')
worksheet = workbook.add_worksheet()
row = 0
col = 0
for row_data in rows:
worksheet.write(row, col, row_data.1)
worksheet.write(row, col+1, row_data.2)
worksheet.write(row, col+2, row_data.3)
row += 1
workbook.close()
This should write rows of data to your spreadsheet. You can view the xlsxwriter tutorial here: https://xlsxwriter.readthedocs.io/tutorial01.html
Just as an aside, you can also access SQL and Excel via ADO (using COM), however, if you're just beginning with Python, that might be a bit more challenging. If you're interested in looking at that later, here's a primer on ADO in python: http://mayukhbose.com/python/ado/index.php
import xlsxwriter
workbook = xlsxwriter.Workbook('your_excel.xlsx')
worksheet = workbook.add_worksheet()
The first version writes one value (i.e. row) in each column sequentially.
for index, row in enumerate(cursor):
worksheet.write(0, index, row)
workbook.close()
The second version writes one value (i.e. row) in each row sequentially.
for index, row in enumerate(cursor):
worksheet.write(index, 0, row)
workbook.close()
row.column where column is the name of the column in your table. So if you have a table with columns username, password then you will substitute that with the value. The Row object is apparently not supported. The types supported are: string, number, formula, None. xlsxwriter.readthedocs.io/worksheet.html#writeIf the Pandas package is available to you, you can easily dump a DataFrame to an Excel spreadsheet. Formatting is also accessible. You'll need to get the data into a DataFrame by appending rows to a list (not sure how pyodbc works). Something like:
import pandas as pd
data = []
...
for rows in cursor:
for row in rows:
data.append(row)
df = pd.DataFrame(data)
Then dump to Excel.
def df_to_excel(path,data_frame):
writer = pd.ExcelWriter(path, engine='xlsxwriter')
data_frame.to_excel(writer, index=False, sheet_name='SHEET0') # sheet 0
writer.save()
Using panda is the best way.. alternatively may use openpyxl
example as below :
# import the nesccesary package
import pyodbc
import openpyxl
# Set connection
connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=127.0.0.1;DATABASE=robbi_database;UID=robbi_username'
';PWD=robbi_password')
cursor = connection.cursor()
# Create excel
book = openpyxl.Workbook()
sheet = book.active
# Export data to excell `
cursor.execute("SELECT TOP 100 FROM robbi_table ORDER BY editdate DESC")
results = cursor.fetchall()
i = 0
for row in results:
i += 1
j = 1
for col in row:
cell = sheet.cell(row=i, column=j)
cell.value = col
j += 1
# Save to excel and close connection
book.save("myExcelFile.xlsx")
cursor.close()
connection.close()
