-
Notifications
You must be signed in to change notification settings - Fork 0
/
autoloader_updater.py
131 lines (114 loc) · 4.26 KB
/
autoloader_updater.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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
# -*- coding: utf-8 -*-
"""
Created on Mon Jan 31 17:02:32 2022
@author: IHiggins
"""
import pyodbc
import pandas as pd
from sqlalchemy import create_engine
import urllib
import configparser
import os
# get access information
# save .ini to local documents, this is not the easiest method or the safest but its better then nothing
access = configparser.ConfigParser()
# get user name for local drive
user = os.getlogin()
access.read(r"C:\Users\"+str(user)+"\OneDrive - King County\Documents\access.ini")
server = access["sql_connection"]["server"]
driver = access["sql_connection"]["driver"]
database = access["sql_connection"]["database"]
trusted_connection = access["sql_connection"]["trusted_connection"]
conn = pyodbc.connect('Driver={'+driver+'};'
'Server='+server+';'
'Database='+database+';'
'Trusted_Connection='+trusted_connection+';')
conn = pyodbc.connect('Driver={SQL Server};'
'Server=KCITSQLDEVNRP01;'
'Database=gData;'
'Trusted_Connection=yes;')
'''
Gage_Lookup = pd.read_sql_query('select G_ID, SITE_CODE from tblGaugeLLID;', conn)
print(Gage_Lookup)
existing_data = conn.execute("select max(D_TimeDate) from tblDischargeGauging WHERE G_ID = "+str(1679)+";").fetchval()
conn.execute('delete from tblDischargeGauging WHERE G_ID = '+str(1679)+' AND D_TimeDate between ? and ?', '10/1/2021','2/1/2022')
conn.commit()
print("last data pre cut "+str(existing_data))
data = pd.read_sql_query('select D_TimeDate, D_Discharge from tblDischargeGauging WHERE G_ID = '+str(1679),conn)
print(data)
conn.close()
'''
#table = "tblConductivityGageRun"
server = "KCITSQLPRNRPX01"
driver = "SQL Server"
database = "gData"
trusted_connection = "yes"
#pyodbc_string = 'Driver={'+driver+'};Server='+server+';Database='+database+';Trusted_Connection='+trusted_connection+';'
conn = pyodbc.connect('Driver={'+driver+'};'
'Server='+server+';'
'Database='+database+';'
'Trusted_Connection='+trusted_connection+';')
sql_alchemy_connection = urllib.parse.quote_plus('DRIVER={'+driver+'}; SERVER='+server+'; DATABASE='+database+'; Trusted_Connection='+trusted_connection+';')
sql_engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % sql_alchemy_connection)
cnxn = sql_engine.raw_connection()
cnxn.close()
sql_alchemy_connection = urllib.parse.quote_plus('DRIVER={'+driver+'}; SERVER='+server+'; DATABASE='+database+'; Trusted_Connection='+trusted_connection+';')
sql_engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % sql_alchemy_connection)
#table = "tblConductivityGageRun"
table = "tblDOGageRun"
# create data for insert
'''
G_ID = 2162
Run_By = 1
Date_Run = "5/25/2022"
File_Name = "33DN_Table1.txt"
Instrument_Offset = 0
Column_Number = 6
UTC_Offset = 0
NumberOfHeaderRows = 3
ParameterID = 5
d = {'G_ID': [G_ID],
'Run_By': [Run_By],
'Date_Run': [Date_Run],
'File_Name': [File_Name],
'Instrument_Offset': [Instrument_Offset],
'Column_Number': [Column_Number],
'UTC_Offset': [UTC_Offset],
'NumberOfHeaderRows': [NumberOfHeaderRows],
'ParameterID': [ParameterID]}
'''
#table = "tblDOGageRun"
#Run_DO_ID:
#G_ID: 2162
#Run_By: 1
#Date_Run: 5/25/2022
#File_Name: 33DN_Table1.txt
#Instrument_Offset: 0
#Column_Number: 6
#UTC_Offset: 0
#NumberOfHeaderRows: 3
#ParameterID: 5
#33DN_Table1.txt
'''
df = pd.DataFrame(data=d)
'''
#print(df)
sql_alchemy_connection = urllib.parse.quote_plus('DRIVER={'+driver+'}; SERVER='+server+'; DATABASE='+database+'; Trusted_Connection='+trusted_connection+';')
sql_engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % sql_alchemy_connection)
cnxn = sql_engine.raw_connection()
#df.to_sql(table, sql_engine, method=None, if_exists='append', index=False)
# try method=multi, None works
# try chunksize int
cnxn.close()
'''
result = sql_engine.execute('SELECT * FROM '
'"tblConductivityGageRun"')
names = sql_engine.table_names()
print(result)
#connection = sql_engine.connect()
#results = connection.execute(stmt).fetchall()
'''
existing_data = pd.read_sql_query(f'select * from {table};', conn)
# uploaded table
print("existing_data")
print(existing_data)