AfdZddlZddlZddlZddlZddlZeje Z dZ dZ dZ dZdZd d Zd Ze d kr edSdS) z? Created on Thu Apr 7 13:10:44 2022 @author: MarkvanKruistum Nc:tjd}tjtj}|tj|||||tjdS)z Initialize logging formatter and handlers. Set logging levels. Parameters ---------- logger : logging.Logger Logger object to use. Returns ------- None. zP%(asctime)s - %(name)s - %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d]N) logging Formatter StreamHandlersysstdoutsetLevelDEBUG setFormatter addHandler)logger formatterhandlers Qc:\Users\BasNoordhoorn\OneDrive - Kurtosis\Documenten\vensters\data_read_write.py init_loggingrs!"tuuI#CJ//G W]### ### g OOGM"""""c8tdtj} ||||d}||d}||d}||d}n+#t $rtdYdSwxYw tjd |||| }n+#t $rtd YdSwxYwtd |S) a Read database details from config file and create a database engine object. Parameters ---------- config_path : str Path to the config file containing the database credentials. programme_name : str Name of the programme. Possible values are 'gemeenten', 'waterschappen', 'rbb'. This determines the database. Returns ------- engine : sqlachemy.engine.Engine SQLAlchemy Engine object with which connections to the database can be made. z,Getting database details from config file...serverdatabaseuserpasswordzReading config file failedNz5mysql+pymysql://{user}:{password}@{server}/{database})rrrrz:Creating database connection from connection string failedz'Database connection created succesfully) r debug configparser ConfigParserread Exception exceptionsql create_engineformat) config_pathprogramme_nameconfigrrrrengines rcreate_db_connectionr%&sI" LL?@@@  & ( (F K   '1.)*5n%f-.)*5 5666"#Z#a#agkv~HNYa#a$b$bcc UVVV LL:;;; Ms$A A==$B%$B%)+C$C=<C=cg}|D]-}tj||}||.tj|}|S)N) sheet_name)pd read_excelappendconcat)pathsheetsdf_listsheetdfs rread_excel_filer1LsSG ]4e 4 4 4r 7  B Irctd tj||}n+#t$rtdYdSwxYwtd|S)NzReading table from database...z&Reading table from the database failedz(Dataframe read from database succesfully)r rr(read_sqlrr) table_namer$r0s r read_from_dbr5Ts~ LL1222 [V , , ABBB LL;<<< Is2$AAcz|jjd|dd|_|S)Nzi{}.T)regex)columnsstrreplacer )r0years r remove_icoder=`s3'' d(;(;Rt'LLBJ IrTcP|rdnd}td||jrtddS |||d|n+#t $rtdYdSwxYwtd dS) a Write a specified DataFrame to a specified table in the database. Can contain an append parameter indicating if rows should be appended or replaced. Parameters ---------- engine : sqlachemy.engine.Engine SQLAlchemy Engine object with which connections to the database can be made. table_name : str Table name that the rows in the DataFrame are to be written to. df : DataFrame DataFrame containing rows to write to the database. append : boolean, optional Specifies if rows should be appended or replaced. The default is True (append). Returns ------- None. r*r;z Writing dataframe to database...NzEmpty or no dataframe given!F)index if_existsz#Writing rows to the database failedz)Dataframe written to database succesfully)r remptyerrorto_sqlrr)r$r4r0r*modes r write_to_dbrEds* ,889D LL3444 zRXz 3444 *fET BBBB >??? LL<=====sA!!$B B c$tddS)Nz.Run one of the runners to read and write data!)printrrmainrIs :;;;;;r__main__)T)__doc__rpandasr( sqlalchemyrrr getLogger__name__r rr%r1r5r=rErIrHrrrPs   8 $ $###*$$$L   #>#>#>#>J<<< zDFFFFFr