python - export or write to excel ms SQL server QUERY

Question

I have a database in sql server called zd and a table called user_tab_columns. I want to export in bulk or write to excel the result of the query statement. The code that I tried to mimic from different sources ended up giving me error messages.

In the database zd and table user_tab_columns, the field are as below:

Here is an example of my code below: ValueError with Pandas - shaped of passed values

error message SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

import pyodbc
import pandas as pd
import os

cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                              "Server=DPC;"
                               "Database=zD;"
                               "trusted_connection=yes;")

cursor = cnxn.cursor()
script = """
SELECT * 
FROM user_tab_columns
WHERE table_name = "A"
"""

cursor.execute(script)

columns = [desc[0] for desc in cursor.description]
data = cursor.fetchall()
df = pd.DataFrame(list(data), columns=columns)

writer = pd.ExcelWriter('C:\Users\PROGRAMs\TEST\export.xlsx')
df.to_excel(writer, sheet_name ='bar')
writer.save()

Kyle · Accepted Answer · 2017-05-01 19:55:27Z

2

I would use pandas' built-in .read_sql(). Also, in order to put " in a string in python, you need to use ' as your quotation as opposed to ".

import pyodbc
import pandas as pd
import os

cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                              "Server=DPC;"
                               "Database=zD;"
                               "trusted_connection=yes;")

cursor = cnxn.cursor()
script = """
SELECT * 
FROM user_tab_columns
WHERE table_name = 'A'
"""

df = pd.read_sql(script, cnxn)

writer = pd.ExcelWriter('C:\Users\PROGRAMs\TEST\export.xlsx')
df.to_excel(writer, sheet_name ='bar')
writer.save()

answered May 1, 2017 at 19:55

Kyle

2,5452 gold badges19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Johnseito Over a year ago

I still have the same error message in this code: writer = pd.ExcelWriter('C:\Users\PROGRAMs\TEST\export.xlsx') SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

Johnseito Over a year ago

Ok I noticed I have to put in double "*\*" for the path to work. This is bulk export, how about if I want more control as in only export 1st or 2nd field, or just the top two row, and no header ? Or how about if I just want to export or write it out iteratively each row and field at a time until the end of the query statement ? Also is giving the row number, how do I get rid of the row number ?

Kyle Over a year ago

You can either use "\\" in the path, or you can use r'C:\Users\PROGRAMs\TEST\export.xlsx'. To change the export, just change the SQL statement to match your criteria.

Johnseito Over a year ago

Thank you, that looks beautiful and it's amazing.

Johnseito Over a year ago

Kyle, your answer was very nice, it's very helpful. But what if I want to export or write the SQL statement into an newly created excel file that is not yet save in any directory, how do I do that then ? I think this is good since sometimes I don't know what directory the user has and I don't want the program to crashed. So it just pops up to a newly created excel file and the user can do what they want with the data, sorry.

Helper · Accepted Answer · 2017-07-11 13:34:22Z

0

use forward slashes (/) instead of backslashes

answered Jul 11, 2017 at 13:34

Helper

1

Collectives™ on Stack Overflow

python - export or write to excel ms SQL server QUERY

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related