| 12345678910111213141516171819202122232425262728293031323334 |
- import pandas as pd
- from sqlalchemy import create_engine
- from database import conn_string
- cfg = {
- 'clients': ['deop01', 'deop02', 'deop03', 'deop04', 'deop05', 'deop06', 'deop07', 'deop08', 'deop09', 'deop10',
- 'deop11', 'deop12', 'deop13', 'deop14', 'deop15', 'deop16', 'deop17', 'deop18', 'deop19', 'deop20',
- 'deop21', 'deop22', 'deop23', 'deop24', 'deop25', 'deop26', 'deop27', 'deop28', 'deop29', 'deho02', 'deni02'],
- 'date_filter': "'2019-01-01'",
- 'source_dsn': {'user': 'sa', 'pass': 'Mffu3011#', 'server': 'GC-SERVER1\\GLOBALCUBE', 'database': 'desk01', 'driver': 'mssql'},
- 'source_schema': 'dbo',
- 'source_query': 'Belege_Planung_Ist_FC.sql',
- 'source_header': ['Datenbank', 'Betrieb_Nr', 'Marke', 'Konto_Nr', 'Bookkeep_Period', 'Betrag', 'Menge'],
- 'target_csv': 'Belege_Planung_Ist_FC.csv'
- }
- def db_import(select_query, source_db, current_table, target_db, target_schema):
- pd.read_sql(select_query, source_db).to_sql(current_table['target'], target_db, schema=target_schema, index=False, if_exists='append')
- source_db = create_engine(conn_string(cfg['source_dsn']))
- with open(cfg['source_query'], 'r') as f:
- select_query = f.read()
- with open(cfg['target_csv'], 'w') as f:
- f.write(';'.join(cfg['source_header']) + '\n')
- for current_client in cfg['clients']:
- current_query = select_query.replace('desk01', current_client)
- pd.read_sql(current_query, source_db).to_csv(cfg['target_csv'], sep=';', decimal=',', encoding='latin-1',
- index=False, header=False, mode='a')
- print(current_client)
|
