-
Notifications
You must be signed in to change notification settings - Fork 0
/
exportExcel.py
59 lines (50 loc) · 1.74 KB
/
exportExcel.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
import pyodbc
import pandas as pd
import os
from datetime import datetime
from plyer import notification
# Parameter koneksi
driver = 'ODBC Driver 17 for SQL Server'
server = ''
database = ''
trusted = 'yes'
# String koneksi
conn_str = (
f"DRIVER={driver};"
f"SERVER={server};"
f"DATABASE={database};"
f"Trusted_Connection={trusted};"
)
try:
#create SQL conn
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
# print(pyodbc.drivers())
# ['SQL Server', 'Microsoft Access Driver (*.mdb, *.accdb)', 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'Microsoft Access Text Driver (*.txt, *.csv)', 'ODBC Driver 17 for SQL Server', 'SQL Server Native Client RDA 11.0']
# SQL command to read data
sqlQuery = "SELECT * FROM dbo.data"
cursor.execute(sqlQuery)
# getting data from sql into pandas dataFrame
columns = [columns[0] for columns in cursor.description]
data = cursor.fetchall()
# df = pd.read_sql(sqlQuery, conn)
df = pd.DataFrame(data, columns=columns)
# close SQL conn
cursor.close()
conn.close()
# initial path
directory = "D:\\##PROJECT\\Python\\SQL_backup_auto"
# create directory if not exist
if not os.path.exists(directory):
os.makedirs(directory)
# merge path with filename
file_path = os.path.join(directory, "SQL_Data_" + datetime.now().strftime("%d-%m-%Y") + ".xlsx")
# export data to path
df.to_excel(file_path, index = False)
# display notification status
notification.notify(title = "Report Status!!!",
message = f"Exported data successfully save into Excel.\
\nTotal Rows: {df.shape[0]} \nTotal Columns: {df.shape[1]}",
timeout = 10)
except Exception as e:
print(e)