U SWX-@sdZddlZddlZddlZddlZddlmZzddlmZWn ek r`ddl mZYnXddl Tddl m Z ddl m Z ddl m Z e reZd Znd ZGd d d eZd dZGdddeZGdddeZdddZeje_dddZdS)a Peewee helper for loading CSV data into a database. Load the users CSV file into the database and return a Model for accessing the data: from playhouse.csv_loader import load_csv db = SqliteDatabase(':memory:') User = load_csv(db, 'users.csv') Provide explicit field types and/or field names: fields = [IntegerField(), IntegerField(), DateTimeField(), DecimalField()] field_names = ['from_acct', 'to_acct', 'timestamp', 'amount'] Payments = load_csv(db, 'payments.csv', fields, field_names) N)contextmanager)StringIO)*)Database)Func)PY3FTc@seZdZeddZdS) _CSVReadercksjd}t|trt|d}n,t|tr4|}|dn|}|dd}tj|f|}|V|rf|dS)NFrrT) isinstance basestringopenrseekcsvreaderclose)self file_or_name reader_kwargsis_filefhrr /csv_utils.py get_reader's     z_CSVReader.get_readerN)__name__ __module__ __qualname__rrrrrrr&src sfdd}|S)Ncsfdd|_|S)Ncs fSNrr field_class field_kwargsrr:z2convert_field..decorator..)field)fnrrr decorator9sz convert_field..decoratorr)rrr$rrr convert_field8sr%c@seZdZdZddgZddgZd!dd Zd d Zee d d ddZ ee d d ddZ ee ddddZeeddddZeedd ddZddZddZddZd S)" RowConvertera Simple introspection utility to convert a CSV file into a list of headers and column types. :param database: a peewee Database object. :param bool has_header: whether the first row of CSV is a header row. :param int sample_size: number of rows to introspect z%Y-%m-%dz%m/%d/%Yz%Y-%m-%d %H:%M:%Sz%Y-%m-%d %H:%M:%S.%fT cCs||_||_||_dSr)database has_header sample_size)rr(r)r*rrr__init__OszRowConverter.__init__c Cs<|D]2}ztj||Wntk r.YqXdSqdSNT)datetimestrptime ValueError)rvalueZformatsZfmtrrr matches_dateTs zRowConverter.matches_dater)defaultcCs|Sr)isdigitrr0rrr is_integer]szRowConverter.is_integerc Cs.z t|Wnttfk r$YnXdSdSr,)floatr/ TypeErrorr4rrris_floatas  zRowConverter.is_float)ZnullcCs|||jSr)r1datetime_formatsr4rrr is_datetimejszRowConverter.is_datetimecCs|||jSr)r1 date_formatsr4rrris_datenszRowConverter.is_datecCsdSr,rr4rrrr2rszRowConverter.defaultc Ksg}|j}|j|f|>}|jr(|d7}t|D] \}}||||jkr0qRq0W5QRX|jrz|d|dd}}nddtt|dD}||fS)aq Extract `self.sample_size` rows from the CSV file and analyze their data-types. :param str file_or_name: A string filename or a file handle. :param reader_kwargs: Arbitrary parameters to pass to the CSV reader. :returns: A 2-tuple containing a list of headers and list of rows read from the CSV file. rNcSsg|] }d|qSzfield_%dr.0irrr sz-RowConverter.extract_rows..)r*rr) enumerateappendrangelen) rrrrowsZ rows_to_readrrBrowheaderrrr extract_rowsvs   zRowConverter.extract_rowscCs|j|j|j|j|jgS)z6Return a list of functions to use when testing values.)r<r:r5r8r2rrrr get_checkss zRowConverter.get_checksc srt|}|}g}t|D]P\}}dd|D}|D]4tfdd|D}t|r6|qq6q|S)a Analyze the given rows and try to determine the type of value stored. :param list rows: A list-of-lists containing one or more rows from a csv file. :returns: A list of peewee Field objects for each column in the CSV. cSsg|]}|dkr|qS)r=rrAvalrrrrCsz(RowConverter.analyze..c3s|]}|VqdSrrrNZcheckrr sz'RowConverter.analyze..)ziprMrDsetallrEr") rrHZ transposedZchecksZ column_typesrBcolumnZcol_valsresultsrrPranalyzeszRowConverter.analyzeN)Tr')rrr__doc__r;r9r+r1r% IntegerFieldr5Z FloatFieldr8Z DateTimeFieldr:Z DateFieldr<Z BareFieldr2rKrMrWrrrrr&>s,             r&c@sBeZdZdZdddZdd Zd d Zd d ZddZddZ dS)Loaderat Load the contents of a CSV file into a database and return a model class suitable for working with the CSV data. :param db_or_model: a peewee Database instance or a Model class. :param file_or_name: the filename of the CSV file *or* a file handle. :param list fields: A list of peewee Field() instances appropriate to the values in the CSV file. :param list field_names: A list of names to use for the fields. :param bool has_header: Whether the first row of the CSV file is a header. :param int sample_size: Number of rows to introspect if fields are not defined. :param converter: A RowConverter instance to use. :param str db_table: Name of table to store data in (if not specified, the table name will be derived from the CSV filename). :param reader_kwargs: Arbitrary arguments to pass to the CSV reader. NTr'Fc Ks||_||_||_||_||_||_| |_t|tr<||_ nt|t rNd|_ n|j |_ t|t r||_ d|_|ptjtj|j d|_nd||_|jjj |_ |jjj|_|jjj|_|jjj|_|jjjr| s|jdd|_|jdd|_dS)Nzdata.csvrr>)rfields field_namesr)r* converterrr r filenamernamerr(modelospathsplitextbasenamedb_table_metaZ sorted_fieldsZsorted_field_namesZauto_increment) r db_or_modelrr[r\r)r*r]re pk_in_csvrrrrr+s4       zLoader.__init__cCstdd|S)Nz [^a-z0-9]+_)resublower)rsrrrclean_field_nameszLoader.clean_field_namecCs|jpt|j|j|jdS)N)r)r*)r]r&r(r)r*rLrrr get_converters zLoader.get_convertercsbjjfj\}}|r0|_nfdd|D_js^fdd|D_dS)Ncsg|]}jqSr)r2r")rAri)r]rrrCsz&Loader.analyze_csv..csg|]}|qSrrnrAcolrLrrrCs)rorKrrrWr[r\)rrJrHr)r]rr analyze_csvszLoader.analyze_csvcCsr|jr |jStt||}d|kr.t|d<nt|dtrFt|d<t|jt f|}|j |j _ |j|j _|S)NidZ_auto_pk) r`dictrRZPrimaryKeyFieldr rYtyperetitleZModelr(rf)rr\r[Zattrsklassrrrget_model_classs    zLoader.get_model_classc sjsjs4js4ddttjD_jjfj}|}jspt |}fdd|D_njr~t | jj}j b| d|D]L}i}tj|D]"\}}|rtr|d}|||<q|r|jf|qW5QRXW5QRX|S)NcSsg|] }d|qSr?rr@rrrrC szLoader.load..csg|]}|qSrrprqrLrrrCsTzutf-8)r[rsr\r)rFrGrrrnextryr(Z transactionZ create_tablerR decode_valuedecodeinsertZexecute)rZ reader_objrrIZ ModelClassr}Z field_namer0rrLrloads4      &z Loader.load)NNTr'NNF) rrrrXr+rnrorsryr~rrrrrZs %  rZr'c Ks*tf|||||||||d | } | S)N) rgrr[r\r)r*r]rerh)rZr~) rgrr[r\r)r*r]rerhrloaderrrrload_csv's  rc Cst|trt||rdpd}n|}|r2|dd|pHtj|ddtjd}|rg}t|jD]F\} } | j rx| | j q\t| t t fr| | j q\| d| q\|||D]} || q|r||S) z' Create a CSV dump of a query. awr,")Z delimiterZ quotecharZquotingzcol_%s)r r r r rwriterZ QUOTE_MINIMALrDZ_selectZ_aliasrEZFieldrr_ZwriterowZtuplesiteratorr) ZqueryrZinclude_headerZ close_filerEZ csv_writerrrrJidxZnoderIrrrdump_csv9s2    r)NNTr'NNF)TTTN)rXrr-rarj contextlibrr ImportErrorioZpeeweerrrstrr r{objectrr%r&rZrrrrrrsB    qx