R6�WX- � @ sk d Z d d l Z d d l Z d d l Z d d l Z d d l m Z y d d l m Z Wn" e k
r~ d d l m Z Yn Xd d l
Td d l
m Z d d l
m Z d d l
m
Z
e
r� e Z d Z n d
Z Gd d � d e � Z d
d � Z Gd d � d e � Z Gd d � d e � Z d d d
d d d d d d � Z e j e _ d
d
d
d d d � Z d S)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 Z d Z e d d � � Z d S)�
_CSVReaderc k s� d } t | t � r' t | d � } n> t | t � rL | } | j d � n | } | j d � d } t j | | � } | V| r� | j � d S)NF�rr T)�
isinstance�
basestring�openr �seek�csv�reader�close)�self� file_or_name�
reader_kwargs�is_file�fhr � 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 � � f d d � } | S)Nc s � � f d d � | _ | S)Nc s
� � � S)Nr r )� field_class� field_kwargsr r �<lambda>: s z2convert_field.<locals>.decorator.<locals>.<lambda>)�field)�fn)r r r r � decorator9 s z convert_field.<locals>.decoratorr )r r r! r )r r r �
convert_field8 s r" c @ s e Z d Z d Z d d g Z d d g Z d d d d � Z d
d � Z e e d d
�d d � � Z
e e d d
�d d � � Z e e
d d �d d � � Z e e d d �d d � � Z e e d d �d d � � Z d d � Z d d � Z d d � Z d 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)N)�database�
has_header� sample_size)r r% r& r' r r r �__init__O s zRowConverter.__init__c C sE x> | D]6 } y t j j | | � Wn t k
r8 Yq Xd Sq Wd S)NT)�datetime�strptime�
ValueError)r �value�formats�fmtr r r � matches_dateT s
zRowConverter.matches_date�defaultr c C s
| j � S)N)�isdigit)r r, r r r �
is_integer] s zRowConverter.is_integerc C s1 y t | � Wn t t f k
r( Yn Xd Sd S)NT)�floatr+ � TypeError)r r, r r r �is_floata s
zRowConverter.is_floatZnullc C s | j | | j � S)N)r/ �datetime_formats)r r, r r r � is_datetimej s zRowConverter.is_datetimec C s | j | | j � S)N)r/ � date_formats)r r, r r r �is_daten s zRowConverter.is_date� c C s d S)NTr )r r, r r r r0 r s zRowConverter.defaultc K s� g } | j } | j | | � �T } | j r7 | d 7} x7 t | � D]) \ } } | j | � | | j k rD PqD WWd QRX| j r� | d | d d � } } n# d d � t t | d � � D� } | | f S)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.
� Nr c S s g | ] } d | � q S)zfield_%dr )�.0�ir r r �
<listcomp>� s z-RowConverter.extract_rows.<locals>.<listcomp>)r' r r& � enumerate�append�range�len) r r r �rowsZ rows_to_readr r= �row�headerr r r � extract_rowsv s
#zRowConverter.extract_rowsc C s" | j | j | j | j | j g S)z6Return a list of functions to use when testing values.)r9 r7 r2 r5 r0 )r r r r �
get_checks� s
zRowConverter.get_checksc s� t | � } | j � } g } x} t | � D]o \ } } d d � | D� } xM | D]E � t � f d d � | D� � } t | � rQ | j � j � � PqQ Wq+ W| 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 k r | � q S)r: r )r< �valr r r r> � s z(RowConverter.analyze.<locals>.<listcomp>c 3 s | ] } � | � Vq d S)Nr )r< rH )�checkr r � <genexpr>� s z'RowConverter.analyze.<locals>.<genexpr>)�ziprG r? �set�allr@ r ) r rC Z
transposedZchecksZ column_typesr= �columnZcol_vals�resultsr )rI r �analyze� s
zRowConverter.analyzeN)r r r �__doc__r8 r6 r( r/ r" � IntegerFieldr2 Z
FloatFieldr5 Z
DateTimeFieldr7 Z DateFieldr9 Z BareFieldr0 rF rG rP r r r r r# > s r# c @ ss e Z d Z d Z d d d d d d d d d � Z d d � Z d
d � Z d d
� Z d d � Z d d � Z d S)�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 sj | | _ | | _ | | _ | | _ | | _ | | _ |
| _ t | t � rZ | | _ n' t | t
� ru d | _ n | j | _ t | t � r� | | _
d | _ | p� t j j t j j | j � � d | _ n� | | _ | j j j
| _
| j j j | _ | j j j | _ | j j j | _ | j j j rf| rf| j d d � | _ | j d d � | _ d S)Nzdata.csvr r; )r �fields� field_namesr& r' � converterr r
r �filenamer �namer r% �model�os�path�splitext�basename�db_table�_metaZ
sorted_fieldsZsorted_field_namesZauto_increment) r � db_or_modelr rT rU r& r' rV r^ � pk_in_csvr r r r r( � s2 + zLoader.__init__c C s t j d d | j � � S)Nz
[^a-z0-9]+�_)�re�sub�lower)r �sr r r �clean_field_name� s zLoader.clean_field_namec C s( | j p' t | j d | j d | j �S)Nr& r' )rV r# r% r& r' )r r r r �
get_converter� s zLoader.get_converterc s� � j � � � j � j � j � \ } } | rE � j | � � _ n � f d d � | D� � _ � j s� � f d d � | D� � _ d S)Nc s g | ] } � j j � � q Sr )r0 r )r< rb )rV r r r> � s z&Loader.analyze_csv.<locals>.<listcomp>c s g | ] } � j | � � q Sr )rg )r< �col)r r r r> � s )rh rF r r rP rT rU )r rE rC r )rV r r � analyze_csv� s zLoader.analyze_csvc C s� | j r | j St t | | � � } d | k rA t � | d <n t | d t � ra t � | d <t | j j � t f | � } | j
| j _
| j | j _ | S)N�idZ_auto_pk) rY �dictrK ZPrimaryKeyFieldr
rR �typer^ �titleZModelr% r_ )r rU rT Zattrs�klassr r r �get_model_class� s
zLoader.get_model_classc ss � j s � j � � j rL � j rL d d � t t � j � � D� � _ � j � j � j � } | �} � j s� t | � } � f d d � | D� � _ n � j r� t | � � j
� j � j � } � j j � �� | j
d � xu | D]m } i } xE t � j | � D]1 \ } } | rt r6| j d � } | | | <qW| r� | j | � j � q� WWd QRXWd QRX| S)Nc S s g | ] } d | � q S)zfield_%dr )r< r= r r r r>
s z Loader.load.<locals>.<listcomp>c s g | ] } � j | � � q Sr )rg )r< ri )r r r r> s Tzutf-8)rT rj rU r& rA rB r r r �nextrp r% Z transactionZ create_tablerK � decode_value�decode�insertZexecute)r Z
reader_objr rD Z
ModelClassrt �
field_namer, r )r r �load s0
%
%z Loader.load)
r r r rQ r( rg rh rj rp rv r r r r rS � s #
rS r$ c K sL t d | d | d | d | d | d | d | d | d | | � }
|
j � S)
Nr` r rT rU r& r' rV r^ ra )rS rv ) r` r rT rU r& r' rV r^ ra r �loaderr r r �load_csv' s rx c C s? t | t � r- t | | r! d p$ d � } n | } | rI | j d d � | pp t j | d d d d d t j �} | rg } xr t | j � D]a \ } }
|
j r� | j
|
j � q� t |
t t f � r� | j
|
j
� q� | j
d
| � q� W| j | � x' | j � j � D] } | j | � qW| r;| j � | S) z'
Create a CSV dump of a query.
�a�wr � � delimiter�,Z quotechar�"Zquotingzcol_%s)r
r r r
r �writerZ
QUOTE_MINIMALr? �_selectZ_aliasr@ ZFieldr rX ZwriterowZtuples�iteratorr ) Zqueryr Zinclude_headerZ
close_filer@ Z
csv_writerr r rE �idxZnoderD r r r �dump_csv9 s0
r� )rQ r r) rZ rc �
contextlibr r � ImportError�ioZpeeweer r r �strr rr �objectr r" r# rS rx r� r r r r �<module> s6
qx