U
SWX- @ s d Z ddlZddlZddlZddlZddlmZ zddlmZ W n ek
r` ddl mZ Y nX ddl
T ddl
m Z ddl
m Z ddl
m
Z
e
reZd Znd
ZG d d d eZd
d ZG dd deZG dd deZdddZej e_ 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 @ s e Zd Zedd ZdS )
_CSVReaderc k sj d}t |trt|d}n,t |tr4|}|d n|}|d d}tj|f|}|V |rf| d S )NFrr T)
isinstance
basestringopenr seekcsvreaderclose)self file_or_name
reader_kwargsis_filefhr r
/csv_utils.py
get_reader' s
z_CSVReader.get_readerN)__name__
__module__ __qualname__r r r r r r r & s r c s fdd}|S )Nc s fdd| _ | S )Nc s
f S Nr r field_class field_kwargsr r <lambda>: z2convert_field.<locals>.decorator.<locals>.<lambda>)field)fnr r r decorator9 s z convert_field.<locals>.decoratorr )r r r$ r r r
convert_field8 s r% c @ s e Zd ZdZddgZddgZd!dd Zd
d Zee d d
dd Z
ee d d
dd Z ee
dddd Zeedddd Zeedd
dd Zdd Zd d Zd d Zd 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
c C s || _ || _|| _d S r )database
has_header sample_size)r r( r) r* r r r __init__O s zRowConverter.__init__c C s< |D ]2}zt j || W n tk
r. Y qX dS qd S NT)datetimestrptime
ValueError)r valueZformatsZfmtr r r matches_dateT s zRowConverter.matches_dater )defaultc C s | S r )isdigitr r0 r r r
is_integer] s zRowConverter.is_integerc C s. z t | W n ttfk
r$ Y nX dS d S r, )floatr/ TypeErrorr4 r r r is_floata s
zRowConverter.is_float)Znullc C s | || jS r )r1 datetime_formatsr4 r r r is_datetimej s zRowConverter.is_datetimec C s | || jS r )r1 date_formatsr4 r r r is_daten s zRowConverter.is_date c C s dS r, r r4 r r r r2 r s zRowConverter.defaultc K s g }| j }| j|f|>}| jr(|d7 }t|D ] \}}|| || j kr0 qRq0W 5 Q R X | jrz|d |dd }}ndd tt|d D }||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.
r Nc S s g | ] }d | qS zfield_%dr .0ir r r
<listcomp> s z-RowConverter.extract_rows.<locals>.<listcomp>)r* r r) enumerateappendrangelen) r r r rowsZ rows_to_readr rB rowheaderr r r extract_rowsv s
zRowConverter.extract_rowsc C s | j | j| j| j| jgS )z6Return a list of functions to use when testing values.)r< r: r5 r8 r2 r r r r
get_checks s zRowConverter.get_checksc sr t | }| }g }t|D ]P\}}dd |D }|D ]4 t fdd|D }t|r6| q q6q |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.
c S s g | ]}|d kr|qS )r= r rA valr r r rC s z(RowConverter.analyze.<locals>.<listcomp>c 3 s | ]} |V qd S r r rN Zcheckr r <genexpr> s z'RowConverter.analyze.<locals>.<genexpr>)ziprM rD setallrE r" ) r rH Z
transposedZchecksZ column_typesrB columnZcol_valsresultsr rP r analyze s zRowConverter.analyzeN)Tr' )r r r __doc__r; r9 r+ r1 r% IntegerFieldr5 Z
FloatFieldr8 Z
DateTimeFieldr: Z DateFieldr< Z BareFieldr2 rK rM rW r r r r r& > s,
r& c @ sB e Zd ZdZdddZdd Zd
d Zd d
Zdd Zdd Z 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
K s || _ || _|| _|| _|| _|| _|
| _t|tr<|| _ nt|t
rNd| _ n|j | _ t|t r|| _
d | _|ptjtj| j d | _nd|| _| jjj
| _
| jjj| _| jjj| _| jjj| _| jjjr| s| jdd | _| jdd | _d S )Nzdata.csvr r> )r fields field_namesr) r* converterr r
r filenamer namer r( modelospathsplitextbasenamedb_table_metaZ
sorted_fieldsZsorted_field_namesZauto_increment) r db_or_modelr r[ r\ r) r* r] re pk_in_csvr r r r r+ s4
zLoader.__init__c C s t dd| S )Nz
[^a-z0-9]+_)resublower)r sr r r clean_field_name s zLoader.clean_field_namec C s | j pt| j| j| jdS )N)r) r* )r] r& r( r) r* rL r r r
get_converter s
zLoader.get_converterc sb jjfj\}}|r0 |_n fdd|D _js^fdd|D _d S )Nc s g | ]} j qS r )r2 r" )rA ri )r] r r rC s z&Loader.analyze_csv.<locals>.<listcomp>c s g | ]} |qS r rn rA colrL r r rC s )ro rK r r rW r[ r\ )r rJ rH r )r] r r analyze_csv s zLoader.analyze_csvc C sr | j r | j S tt||}d|kr.t |d< nt|d trFt |d< t| j t f|}| j
|j _
| j|j _|S )NidZ_auto_pk) r` dictrR ZPrimaryKeyFieldr
rY typere titleZModelr( rf )r r\ r[ Zattrsklassr r r get_model_class s
zLoader.get_model_classc
s j s js4 js4dd tt j D _ j jf j}|} jspt |} fdd|D _n jr~t |
j j } j b |
d |D ]L}i }t j|D ]"\}}|rtr|d}|||< q|r|jf | qW 5 Q R X W 5 Q R X |S )Nc S s g | ] }d | qS r? r r@ r r r rC s z Loader.load.<locals>.<listcomp>c s g | ]} |qS r rp rq rL r r rC s Tzutf-8)r[ rs r\ r) rF rG r r r nextry r( Z transactionZ create_tablerR decode_valuedecodeinsertZexecute)r Z
reader_objr rI Z
ModelClassr} Z
field_namer0 r rL r load s4
&z Loader.load)NNTr' NNF)
r r r rX r+ rn ro rs ry r~ r r r r rZ s
%
rZ r' c K s* t f | ||||||||d | }
|
S )N) rg r r[ r\ r) r* r] re rh )rZ r~ ) rg r r[ r\ r) r* r] re rh r loaderr r r load_csv' s
r c C s t |tr t||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
r writerZ
QUOTE_MINIMALrD Z_selectZ_aliasrE ZFieldr r_ ZwriterowZtuplesiteratorr ) Zqueryr Zinclude_headerZ
close_filerE Z
csv_writerr r rJ idxZnoderI r r r dump_csv9 s2
r )NNTr' NNF)TTTN)rX r r- ra rj
contextlibr r ImportErrorioZpeeweer r r strr r{ objectr r% r& rZ r r r r r r <module> sB qx