`9Sc@sddlZddlZddlZddlZddlZddlZddlZddlZddl m Z ddl m Z ddl m Z ddgZ dfdYZdfdYZed Zed Zd Zd ZdS( iN(taccess(texcel(ttxtt DataRecordt DataFieldcBseZdZdedZRS(sDescription of a class instance attribute mapped to a database field or optionally to a column of an Excel worksheet. Attributes: - 'attributeName': name of the attribute; - 'fieldName': name of the corresponding database field, or Excel file column header label (if the data are to be read from an Excel file); - 'dataType': name of the SQL data type for the database field (see 'access.getDataTypeCode', 'access.getDataTypeName'). If the data are read from an Excel file instead of a database, this is used as a hint in cell format checking; - 'arrayDataType': Numpy array data type for array-like attributes. Not used unless 'dataType' is 'LONGBINARY'. - 'required': 'True' if this data field is mandatory for input database files and 'False' if the data field is optional. In the latter case, the value of the attribute would be set to 'None' if the corresponding database field is absent.cCs1||_||_||_||_||_dS(N(t attributeNamet fieldNametdataTypet arrayDataTypetrequired(tselfRRRRR ((scode\common\DataRecord.pyt__init__5s     N(t__name__t __module__t__doc__tNonetTrueR (((scode\common\DataRecord.pyR"scBs:eZdZdZgZgZdZdZdZ dZ dZ dZ dZ dZdZdZdZdZdZedZedZedZedZed Zed Zed Zed Zed ZedZedZ edZ!dZ"dZ#RS(sBase class for data structures intended for serialization in Microsoft Access database files or in Microsoft Excel files. Primary class attributes: - 'tableName': string holding the name of the database table to store the data in (or the name of the Excel worksheet if the data are to be read from an Excel file); - 'fields': list of 'DataField' instances describing the structure of the data records; Class attributes related to operations with database files (see 'createDatabase', 'checkDataFormat', 'readDataList', and 'writeData'): - 'extraFieldNames': list of names of required database fields that are not referenced in 'fields' (this is an empty list by default); - 'selectStatementExtraText': string to be appended to 'SELECT FROM ' command when querying the data from a database (this is an empty string by default); - 'createDatabaseErrorMessage': error string for database file creation failure; - 'openDatabaseErrorMessage': error string for failure opening a database file for reading; - 'queryFailureErrorMessage': error string for failure executing the SQL 'SELECT' statement on an opened database file; - 'invalidFormatErrorPrefix': prefix for error strings reporting failed database structure tests, ending with a colon and space; - 'writeDataErrorMessage': error string for failure adding a new record to a database file; Class attributes related to reading data from an Excel file instead of a database (see 'readDataListFromExcel'): - 'extraExcelHeaderRows': number of Excel file rows that are located above the main header row and may contain some additional information that doesn't need to be checked by the file format testing procedure (this is zero by default); - 'openExcelFileErrorMessage': error string for failure opening an Excel file for reading; - 'invalidExcelFormatErrorPrefix': prefix for error strings reporting failed Excel file structure tests, ending with a colon and space; Class attributes related to Excel import and export operations (see 'importDataListFromExcel', 'exportDataListToExcel' and 'exportDataToExcel'): - 'importDataFromExcelErrorPrefix': prefix for error strings reporting failed Excel import; - 'exportDataToExcelErrorPrefix': prefix for error strings reporting failed Excel export. Primary class attributes ('tableName' and 'fields') must be redefined in the derived class. Class attributes related to error messages must be redefined unless code capable of generating an error will never be called. 'onDataLoaded' virtual method may be redefined in order to add some custom processing to the data loading procedure. The differences between reading from an Excel file and importing from an Excel file are as follows: - Reading operation does not support array data fields (see 'DataField.arrayDataType'), but is more flexible with regard to layout of the data cells: it allows to read Excel files with extra rows above the main header (see 'extraExcelHeaderRows'). On the other hand, Excel file format for import/export is fixed and optimized for export of databases containing array data fields. It will contain exactly the same number of data fields as defined in the 'fields' class attribute, and a special numbering column will be included in the main worksheet to facilitate location of the corresponding array data; - For clarity, error messages used in these two operations differ a bit.ticCs+x$|jD]}t||dq WdS(sKInitialize all the attributes referenced in 'cls.fields' to 'None'.N(tgetAttributeNamestsetattrR(R tname((scode\common\DataRecord.pyR scCsdS(sCalled by 'readDataList' and similar functions after all the data record's attributes are read from a database or an Excel file. Redefine this method in the derived class to add any custom processing to the data loading procedure.N((R ((scode\common\DataRecord.pyt onDataLoadedscCsg|jD]}|j^q S(s>Return the list of attribute names referenced in 'cls.fields'.(tfieldsR(tclstfield((scode\common\DataRecord.pyRscCs&g|jD]}|jr |j^q S(stReturn the list of attribute names referenced in 'cls.fields' whose 'required' attributes are set to 'True'.(RR R(RR((scode\common\DataRecord.pytgetRequiredAttributeNamesscCs.x'|jD]}|j|kr |jSq WdS(saReturn the name of the database field corresponding to the attribute with the given name.N(RRR(RRR((scode\common\DataRecord.pyt getFieldNamescCsydtj|}zCdjd|jD}|jdtj|j|fWd|jXWn&tj k rt j |j nXdS(sZCreate a new empty database file at the given path. Raise 'txt.Error' on failure.s, css,|]"}tj|jd|jVqdS(t N(Rt quoteNameRR(t.0R((scode\common\DataRecord.pys ssCREATE TABLE %s (%s)N( RtcreateDatabasetjoinRtExecuteRt tableNametCloset pywintypest com_errorRtErrortcreateDatabaseErrorMessage(Rt dbFilePatht dbConnectiontfieldsDefinition((scode\common\DataRecord.pyRs c sytj|}Wn&tjk r;tj|jnXyStj|}|j|krtj|j dtj |jntj ||jxi|j g|j D]}|jr|j^qD]8}|krtj|j dtj |qqWy^djfd|j D}d|tj|jf|j}|j|\}} Wn&tjk rtj|jnXWn|jnXy|j} x|j D]}|jp|jksqn| j|jj} | tj|jkrtj|j} | dk s1ttj|j dtj |j| fqqWWn|j|jnX||fS(sSame as 'checkDataFormat', but return a tuple of opened database connection, opened Recordset pointing to the data, and a list of the database field names.sthere is no table named %ss!the database contains no %s fields, c3s9|]/}|js!|jkrtj|jVqdS(N(R RRR(RR(t dbFieldNames(scode\common\DataRecord.pys ssSELECT %s FROM %ss2type of the %s database field is different from %sN(Rt openDatabaseR#R$RR%topenDatabaseErrorMessaget getTableNamesR!tinvalidFormatErrorPrefixtquotet getFieldNamestextraFieldNamesRR RRRtselectStatementExtraTextR tqueryFailureErrorMessageR"tFieldstItemtTypetgetDataTypeCodeRtgetDataTypeNameRtAssertionError( RR'R(t dbTableNamesRRt fieldListtquerytrecSettrecordsAffectedtdbFieldstdbFieldTypeCodet dataTypeName((R*scode\common\DataRecord.pyt_checkDataFormatImplsV  -      (  cCs0|j|\}}}|j|jdS(sAssure that a file at the given path is a valid Access database and that its format matches the data structure. Raise 'txt.Error' on failure.N(RBR"(RR'R(R=R*((scode\common\DataRecord.pytcheckDataFormat+s c Cs|j|\}}}zg}x|js|j}|}xQ|jD]F}|j|krgd} n|j|jj} |jdkr| dk rt | t st t j |jj} t| | dkrt jd} q|t j| d|j} nx|jdkr@| dk r@tj| j| j| j} n<|jdkr|| dk r|tj| j| j| j} nt||j| qIW|j|j||j q$WWd|j!|j!X|S(sUOpen a database file at the given path for reading, check its format, query the available data and return them as a list of 'cls' instances. Raise 'txt.Error' on failure. Date and time values are stored as 'datetime.date' and 'datetime.time' instances; long binary values are stored as Numpy arrays.t LONGBINARYitdtypetDATEtTIMEN("RBtEOFR4RRRR5tValueRt isinstancetbufferR9tnumpyRERtitemsizetlentzerost frombuffertdatetimetdatetyeartmonthtdayttimethourtminutetsecondRRRtappendtMoveNextR"( RR'R(R=R*t recordListR?t dataRecordRtvaluetitemSize((scode\common\DataRecord.pyt readDataList7s>          c Csytj|dt}Wn,ttjfk rGtj|jnX|j|j krtj|j dtj |jn|j |j}|j d}|j|}|dkrtj|j dtj |jni}xtd|jD]}|j|d|}|j|d|} | tjkrGqn||krtj|j dtj tj|d||tj tj|d|tj |fn|||tt|D]*} t|d| d|| jd |q>Wg} |j d} xt| D]} | d} t|| d| dd ||}xZtt|D]F} t|| | d|| j ||j}t||| j|qWt|dkr)tt|| | \}}||jkr{tjtd|n|j |}xtt|D]} t|d|| | dd |t|d|| || jd |t|d || || j|}t||| j|qWn|j| j|qW| S( sASame as 'importDataListFromExcel', but use a custom error prefix.sfile format is invalid: Ras4the file is invalid or may not be opened for readingsthere is no worksheet named %sis%s worksheet contains no dataRDitVARCHARtINTi(RbRcRRdReRR%R!RgR/RiRkRRRlRNt_testExcelCellRRrRsRRt_getArrayWorkSheett errorPrefixt_readExcelArrayRRRZ(RRtterrorMessagePrefixtinvalidFormatPrefixRuRvRt scalarFieldst arrayFieldstiR\t totalRecordsR}R~R]R^tarraySheetNametarrayColtarrayWorkSheet((scode\common\DataRecord.pyt_importDataListFromExcelImplsf             cCs$|j||jdtj|S(sReverse of 'exportDataListToExcel'. Similar to 'readDataList', but read the data from a strictly formed Excel file instead of a database.s (%s): (RtimportDataFromExcelErrorPrefixRt quotePath(RRt((scode\common\DataRecord.pytimportDataListFromExcelAs  c Cstj}|j|j}g|jD]}|jdkr(|^q(}g|jD]}|jdkrS|^qS}xdtt|D]P}|jd|d||j ||jdkrd |j |d_ qqWd } xTtt|D]@} || } | d} |j| d| dxtt|D]}t | ||j} ||jdkr| d k rtj}d|_|j| |d| |q8||jdkr| d k rtj}d |_|j| |d| |q8|j| |d| q8Wt|dkrtt|| t|\}}| d ksb| j|krt|j|} nxtt|D]}| jd||| d| jd||||j t | ||j}|d k rxBtt|D]+} | j| d ||t|| qWqqWqqWy|j|Wn4tk rtj|jd tj|nXd S(sWrite the given list of 'cls' instances to an Excel file compatible with 'importDataListFromExcel'. Raise 'txt.Error' on failure.RDiiRFii s yyyy-mm-ddRGshh:mm:ssis (%s)Ni (txlwttWorkbookt add_sheetR!RRRlRNtwriteRRztwidthRtgetattrRtXFStyletnum_format_strRRtfloattsavet ExceptionRR%texportDataToExcelErrorPrefixR(RRtR\RuRvRRRRRR}R]R~R^tstyleRRt arrayData((scode\common\DataRecord.pytexportDataListToExcelLs^       !  # c Csytj|}zltjjjd}|j|j|ddz,g|jD]}|j ^qP}g}x|jD]}t ||j }|j dkr|d k rt|j}n|j dkr|d k rtj|j|j|jdddf}nH|j dkrM|d k rMtjddd |j|j|jf}n|j|quW|j||Wd |jXWd |jXWn&tjk rtj|jnXd S( sAppend the 'self' data record to a database file at the given path. Raise 'txt.Error' on failure. Note: no thorough examination of the database file structure is carried out; use 'checkDataFormat' for that.sADODB.RecordsetiiRDRFRGiki iN(RR+twin32comtclienttgencachetEnsureDispatchtOpenR!RRRRRRRKttostringR#tTimeRSRTRURWRXRYRZtAddNewR"R$RR%twriteDataErrorMessage(R R'R(R=RR;t valueListR^((scode\common\DataRecord.pyt writeDatas2  'cCsbtjj|r;|j||jdtj|}ng}|j||j||dS(sAppend the 'self' data record to an Excel file at the given path. Raise 'txt.Error' on failure. Warning: this function actually imports the entire file and then writes the data back, thus being quite time-consuming on large files.s (%s): N( RRtexistsRRRRRZR(R RtR\((scode\common\DataRecord.pytexportDataToExcels    N($R R RRR!RR1R2R&R,R3R.RRjRfRhRRR Rt classmethodRRRRRBRCR`Rt staticmethodRRRRRR(((scode\common\DataRecord.pyR?s>E   S HXc ` s&          G  %