-
Notifications
You must be signed in to change notification settings - Fork 0
/
get_brands_db_wx.py
66 lines (56 loc) · 2.04 KB
/
get_brands_db_wx.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
60
61
62
63
64
65
66
# get_brands_db_wx.py
# import library components ---------------------------------------------------
# import shutil
import os
import pyodbc
import pathlib
from pathlib import Path
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import numpy as np
def main():
# get path of current folder
folder_path = os.path.dirname(os.path.abspath(__file__))
# identify i/o ----------------------------------------------------------------
outfile_name = 'db_brands_wx.xlsx'
outfile_path = folder_path + '\\' + outfile_name
# connection & query info -----------------------------------------------------
print('\nConnecting to database...')
server = 'sql.wrangle.works'
database = 'Wrangleworks'
username = 'stacy'
password = '8d39c!76b8d1'
connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
# cursor info
cursor = connection.cursor()
# query info
brand_query = ['''
SELECT DISTINCT ID
FROM Brands
ORDER BY ID ASC
''']
brands = []
# fetch brands from database --------------------------------------------------
# fetch brands, append them to pandas array, and print them to console
print('\nQuerying the database...')
cursor.execute(brand_query[0]) # reset the cursor
row = cursor.fetchone()
row_count = 0
while row:
for i in row:
print('{}'.format(i), end='')
brands.append(i)
row_count += 1
row = cursor.fetchone()
print('')
# print query results to excel file -------------------------------------------
df = pd.DataFrame({'BRAND':brands})
writer = pd.ExcelWriter(outfile_path)
df.to_excel(writer,'Sheet1', index=False)
writer.save()
# end program -----------------------------------------------------------------
print('\n{} Brands written'.format(row_count))
print(outfile_path)
print('Done.')
if __name__ == '__main__' : main()