<s> """<STR_LIT>""" <EOL> from __future__ import print_function , division <EOL> from datetime import datetime , date , time <EOL> import warnings <EOL> import traceback <EOL> import re <EOL> import numpy as np <EOL> import pandas . lib as lib <EOL> import pandas . core . common as com <EOL> from pandas . compat import ( lzip , map , zip , raise_with_traceback , <EOL> string_types , text_type ) <EOL> from pandas . core . api import DataFrame , Series <EOL> from pandas . core . common import isnull <EOL> from pandas . core . base import PandasObject <EOL> from pandas . types . api import DatetimeTZDtype <EOL> from pandas . tseries . tools import to_datetime <EOL> from contextlib import contextmanager <EOL> class SQLAlchemyRequired ( ImportError ) : <EOL> pass <EOL> class DatabaseError ( IOError ) : <EOL> pass <EOL> _SQLALCHEMY_INSTALLED = None <EOL> def _is_sqlalchemy_connectable ( con ) : <EOL> global _SQLALCHEMY_INSTALLED <EOL> if _SQLALCHEMY_INSTALLED is None : <EOL> try : <EOL> import sqlalchemy <EOL> _SQLALCHEMY_INSTALLED = True <EOL> from distutils . version import LooseVersion <EOL> ver = LooseVersion ( sqlalchemy . __version__ ) <EOL> if ver < '<STR_LIT>' : <EOL> from sqlalchemy import BigInteger <EOL> from sqlalchemy . ext . compiler import compiles <EOL> @ compiles ( BigInteger , '<STR_LIT>' ) <EOL> def compile_big_int_sqlite ( type_ , compiler , ** kw ) : <EOL> return '<STR_LIT>' <EOL> except ImportError : <EOL> _SQLALCHEMY_INSTALLED = False <EOL> if _SQLALCHEMY_INSTALLED : <EOL> import sqlalchemy <EOL> return isinstance ( con , sqlalchemy . engine . Connectable ) <EOL> else : <EOL> return False <EOL> def _convert_params ( sql , params ) : <EOL> """<STR_LIT>""" <EOL> args = [ sql ] <EOL> if params is not None : <EOL> if hasattr ( params , '<STR_LIT>' ) : <EOL> args += [ params ] <EOL> else : <EOL> args += [ list ( params ) ] <EOL> return args <EOL> def _handle_date_column ( col , format = None ) : <EOL> if isinstance ( format , dict ) : <EOL> return to_datetime ( col , errors = '<STR_LIT:ignore>' , ** format ) <EOL> else : <EOL> if format in [ '<STR_LIT:D>' , '<STR_LIT:s>' , '<STR_LIT>' , '<STR_LIT>' , '<STR_LIT>' ] : <EOL> return to_datetime ( col , errors = '<STR_LIT>' , unit = format , utc = True ) <EOL> elif ( issubclass ( col . dtype . type , np . floating ) or <EOL> issubclass ( col . dtype . type , np . integer ) ) : <EOL> format = '<STR_LIT:s>' if format is None else format <EOL> return to_datetime ( col , errors = '<STR_LIT>' , unit = format , utc = True ) <EOL> elif com . is_datetime64tz_dtype ( col ) : <EOL> return ( to_datetime ( col , errors = '<STR_LIT>' ) <EOL> . astype ( '<STR_LIT>' ) ) <EOL> else : <EOL> return to_datetime ( col , errors = '<STR_LIT>' , format = format , utc = True ) <EOL> def _parse_date_columns ( data_frame , parse_dates ) : <EOL> """<STR_LIT>""" <EOL> if parse_dates is True or parse_dates is None or parse_dates is False : <EOL> parse_dates = [ ] <EOL> if not hasattr ( parse_dates , '<STR_LIT>' ) : <EOL> parse_dates = [ parse_dates ] <EOL> for col_name in parse_dates : <EOL> df_col = data_frame [ col_name ] <EOL> try : <EOL> fmt = parse_dates [ col_name ] <EOL> except TypeError : <EOL> fmt = None <EOL> data_frame [ col_name ] = _handle_date_column ( df_col , format = fmt ) <EOL> for col_name , df_col in data_frame . iteritems ( ) : <EOL> if com . is_datetime64tz_dtype ( df_col ) : <EOL> data_frame [ col_name ] = _handle_date_column ( df_col ) <EOL> return data_frame <EOL> def _wrap_result ( data , columns , index_col = None , coerce_float = True , <EOL> parse_dates = None ) : <EOL> """<STR_LIT>""" <EOL> frame = DataFrame . from_records ( data , columns = columns , <EOL> coerce_float = coerce_float ) <EOL> _parse_date_columns ( frame , parse_dates ) <EOL> if index_col is not None : <EOL> frame . set_index ( index_col , inplace = True ) <EOL> return frame <EOL> def execute ( sql , con , cur = None , params = None ) : <EOL> """<STR_LIT>""" <EOL> if cur is None : <EOL> pandas_sql = pandasSQL_builder ( con ) <EOL> else : <EOL> pandas_sql = pandasSQL_builder ( cur , is_cursor = True ) <EOL> args = _convert_params ( sql , params ) <EOL> return pandas_sql . execute ( * args ) <EOL> def _safe_fetch ( cur ) : <EOL> try : <EOL> result = cur . fetchall ( ) <EOL> if not isinstance ( result , list ) : <EOL> result = list ( result ) <EOL> return result <EOL> except Exception as e : <EOL> excName = e . __class__ . __name__ <EOL> if excName == '<STR_LIT>' : <EOL> return [ ] <EOL> def tquery ( sql , con = None , cur = None , retry = True ) : <EOL> """<STR_LIT>""" <EOL> warnings . warn ( <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" , <EOL> FutureWarning , stacklevel = <NUM_LIT:2> ) <EOL> cur = execute ( sql , con , cur = cur ) <EOL> result = _safe_fetch ( cur ) <EOL> if con is not None : <EOL> try : <EOL> cur . close ( ) <EOL> con . commit ( ) <EOL> except Exception as e : <EOL> excName = e . __class__ . __name__ <EOL> if excName == '<STR_LIT>' : <EOL> print ( '<STR_LIT>' ) <EOL> else : <EOL> raise <EOL> traceback . print_exc ( ) <EOL> if retry : <EOL> return tquery ( sql , con = con , retry = False ) <EOL> if result and len ( result [ <NUM_LIT:0> ] ) == <NUM_LIT:1> : <EOL> result = list ( lzip ( * result ) [ <NUM_LIT:0> ] ) <EOL> elif result is None : <EOL> result = [ ] <EOL> return result <EOL> def uquery ( sql , con = None , cur = None , retry = True , params = None ) : <EOL> """<STR_LIT>""" <EOL> warnings . warn ( <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" , <EOL> FutureWarning , stacklevel = <NUM_LIT:2> ) <EOL> cur = execute ( sql , con , cur = cur , params = params ) <EOL> result = cur . rowcount <EOL> try : <EOL> con . commit ( ) <EOL> except Exception as e : <EOL> excName = e . __class__ . __name__ <EOL> if excName != '<STR_LIT>' : <EOL> raise <EOL> traceback . print_exc ( ) <EOL> if retry : <EOL> print ( '<STR_LIT>' ) <EOL> return uquery ( sql , con , retry = False ) <EOL> return result <EOL> def read_sql_table ( table_name , con , schema = None , index_col = None , <EOL> coerce_float = True , parse_dates = None , columns = None , <EOL> chunksize = None ) : <EOL> """<STR_LIT>""" <EOL> con = _engine_builder ( con ) <EOL> if not _is_sqlalchemy_connectable ( con ) : <EOL> raise NotImplementedError ( "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> import sqlalchemy <EOL> from sqlalchemy . schema import MetaData <EOL> meta = MetaData ( con , schema = schema ) <EOL> try : <EOL> meta . reflect ( only = [ table_name ] , views = True ) <EOL> except sqlalchemy . exc . InvalidRequestError : <EOL> raise ValueError ( "<STR_LIT>" % table_name ) <EOL> pandas_sql = SQLDatabase ( con , meta = meta ) <EOL> table = pandas_sql . read_table ( <EOL> table_name , index_col = index_col , coerce_float = coerce_float , <EOL> parse_dates = parse_dates , columns = columns , chunksize = chunksize ) <EOL> if table is not None : <EOL> return table <EOL> else : <EOL> raise ValueError ( "<STR_LIT>" % table_name , con ) <EOL> def read_sql_query ( sql , con , index_col = None , coerce_float = True , params = None , <EOL> parse_dates = None , chunksize = None ) : <EOL> """<STR_LIT>""" <EOL> pandas_sql = pandasSQL_builder ( con ) <EOL> return pandas_sql . read_query ( <EOL> sql , index_col = index_col , params = params , coerce_float = coerce_float , <EOL> parse_dates = parse_dates , chunksize = chunksize ) <EOL> def read_sql ( sql , con , index_col = None , coerce_float = True , params = None , <EOL> parse_dates = None , columns = None , chunksize = None ) : <EOL> """<STR_LIT>""" <EOL> pandas_sql = pandasSQL_builder ( con ) <EOL> if isinstance ( pandas_sql , SQLiteDatabase ) : <EOL> return pandas_sql . read_query ( <EOL> sql , index_col = index_col , params = params , <EOL> coerce_float = coerce_float , parse_dates = parse_dates , <EOL> chunksize = chunksize ) <EOL> try : <EOL> _is_table_name = pandas_sql . has_table ( sql ) <EOL> except : <EOL> _is_table_name = False <EOL> if _is_table_name : <EOL> pandas_sql . meta . reflect ( only = [ sql ] ) <EOL> return pandas_sql . read_table ( <EOL> sql , index_col = index_col , coerce_float = coerce_float , <EOL> parse_dates = parse_dates , columns = columns , chunksize = chunksize ) <EOL> else : <EOL> return pandas_sql . read_query ( <EOL> sql , index_col = index_col , params = params , <EOL> coerce_float = coerce_float , parse_dates = parse_dates , <EOL> chunksize = chunksize ) <EOL> def to_sql ( frame , name , con , flavor = '<STR_LIT>' , schema = None , if_exists = '<STR_LIT>' , <EOL> index = True , index_label = None , chunksize = None , dtype = None ) : <EOL> """<STR_LIT>""" <EOL> if if_exists not in ( '<STR_LIT>' , '<STR_LIT:replace>' , '<STR_LIT>' ) : <EOL> raise ValueError ( "<STR_LIT>" . format ( if_exists ) ) <EOL> pandas_sql = pandasSQL_builder ( con , schema = schema , flavor = flavor ) <EOL> if isinstance ( frame , Series ) : <EOL> frame = frame . to_frame ( ) <EOL> elif not isinstance ( frame , DataFrame ) : <EOL> raise NotImplementedError ( "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> pandas_sql . to_sql ( frame , name , if_exists = if_exists , index = index , <EOL> index_label = index_label , schema = schema , <EOL> chunksize = chunksize , dtype = dtype ) <EOL> def has_table ( table_name , con , flavor = '<STR_LIT>' , schema = None ) : <EOL> """<STR_LIT>""" <EOL> pandas_sql = pandasSQL_builder ( con , flavor = flavor , schema = schema ) <EOL> return pandas_sql . has_table ( table_name ) <EOL> table_exists = has_table <EOL> _MYSQL_WARNING = ( "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> def _engine_builder ( con ) : <EOL> """<STR_LIT>""" <EOL> global _SQLALCHEMY_INSTALLED <EOL> if isinstance ( con , string_types ) : <EOL> try : <EOL> import sqlalchemy <EOL> con = sqlalchemy . create_engine ( con ) <EOL> return con <EOL> except ImportError : <EOL> _SQLALCHEMY_INSTALLED = False <EOL> return con <EOL> def pandasSQL_builder ( con , flavor = None , schema = None , meta = None , <EOL> is_cursor = False ) : <EOL> """<STR_LIT>""" <EOL> con = _engine_builder ( con ) <EOL> if _is_sqlalchemy_connectable ( con ) : <EOL> return SQLDatabase ( con , schema = schema , meta = meta ) <EOL> else : <EOL> if flavor == '<STR_LIT>' : <EOL> warnings . warn ( _MYSQL_WARNING , FutureWarning , stacklevel = <NUM_LIT:3> ) <EOL> return SQLiteDatabase ( con , flavor , is_cursor = is_cursor ) <EOL> class SQLTable ( PandasObject ) : <EOL> """<STR_LIT>""" <EOL> def __init__ ( self , name , pandas_sql_engine , frame = None , index = True , <EOL> if_exists = '<STR_LIT>' , prefix = '<STR_LIT>' , index_label = None , <EOL> schema = None , keys = None , dtype = None ) : <EOL> self . name = name <EOL> self . pd_sql = pandas_sql_engine <EOL> self . prefix = prefix <EOL> self . frame = frame <EOL> self . index = self . _index_name ( index , index_label ) <EOL> self . schema = schema <EOL> self . if_exists = if_exists <EOL> self . keys = keys <EOL> self . dtype = dtype <EOL> if frame is not None : <EOL> self . table = self . _create_table_setup ( ) <EOL> else : <EOL> self . table = self . pd_sql . get_table ( self . name , self . schema ) <EOL> if self . table is None : <EOL> raise ValueError ( "<STR_LIT>" % name ) <EOL> def exists ( self ) : <EOL> return self . pd_sql . has_table ( self . name , self . schema ) <EOL> def sql_schema ( self ) : <EOL> from sqlalchemy . schema import CreateTable <EOL> return str ( CreateTable ( self . table ) . compile ( self . pd_sql . connectable ) ) <EOL> def _execute_create ( self ) : <EOL> self . table = self . table . tometadata ( self . pd_sql . meta ) <EOL> self . table . create ( ) <EOL> def create ( self ) : <EOL> if self . exists ( ) : <EOL> if self . if_exists == '<STR_LIT>' : <EOL> raise ValueError ( "<STR_LIT>" % self . name ) <EOL> elif self . if_exists == '<STR_LIT:replace>' : <EOL> self . pd_sql . drop_table ( self . name , self . schema ) <EOL> self . _execute_create ( ) <EOL> elif self . if_exists == '<STR_LIT>' : <EOL> pass <EOL> else : <EOL> raise ValueError ( <EOL> "<STR_LIT>" . format ( self . if_exists ) ) <EOL> else : <EOL> self . _execute_create ( ) <EOL> def insert_statement ( self ) : <EOL> return self . table . insert ( ) <EOL> def insert_data ( self ) : <EOL> if self . index is not None : <EOL> temp = self . frame . copy ( ) <EOL> temp . index . names = self . index <EOL> try : <EOL> temp . reset_index ( inplace = True ) <EOL> except ValueError as err : <EOL> raise ValueError ( <EOL> "<STR_LIT>" . format ( err ) ) <EOL> else : <EOL> temp = self . frame <EOL> column_names = list ( map ( text_type , temp . columns ) ) <EOL> ncols = len ( column_names ) <EOL> data_list = [ None ] * ncols <EOL> blocks = temp . _data . blocks <EOL> for i in range ( len ( blocks ) ) : <EOL> b = blocks [ i ] <EOL> if b . is_datetime : <EOL> d = b . values . astype ( '<STR_LIT>' ) . astype ( object ) <EOL> else : <EOL> d = np . array ( b . get_values ( ) , dtype = object ) <EOL> if b . _can_hold_na : <EOL> mask = isnull ( d ) <EOL> d [ mask ] = None <EOL> for col_loc , col in zip ( b . mgr_locs , d ) : <EOL> data_list [ col_loc ] = col <EOL> return column_names , data_list <EOL> def _execute_insert ( self , conn , keys , data_iter ) : <EOL> data = [ dict ( ( k , v ) for k , v in zip ( keys , row ) ) for row in data_iter ] <EOL> conn . execute ( self . insert_statement ( ) , data ) <EOL> def insert ( self , chunksize = None ) : <EOL> keys , data_list = self . insert_data ( ) <EOL> nrows = len ( self . frame ) <EOL> if nrows == <NUM_LIT:0> : <EOL> return <EOL> if chunksize is None : <EOL> chunksize = nrows <EOL> elif chunksize == <NUM_LIT:0> : <EOL> raise ValueError ( '<STR_LIT>' ) <EOL> chunks = int ( nrows / chunksize ) + <NUM_LIT:1> <EOL> with self . pd_sql . run_transaction ( ) as conn : <EOL> for i in range ( chunks ) : <EOL> start_i = i * chunksize <EOL> end_i = min ( ( i + <NUM_LIT:1> ) * chunksize , nrows ) <EOL> if start_i >= end_i : <EOL> break <EOL> chunk_iter = zip ( * [ arr [ start_i : end_i ] for arr in data_list ] ) <EOL> self . _execute_insert ( conn , keys , chunk_iter ) <EOL> def _query_iterator ( self , result , chunksize , columns , coerce_float = True , <EOL> parse_dates = None ) : <EOL> """<STR_LIT>""" <EOL> while True : <EOL> data = result . fetchmany ( chunksize ) <EOL> if not data : <EOL> break <EOL> else : <EOL> self . frame = DataFrame . from_records ( <EOL> data , columns = columns , coerce_float = coerce_float ) <EOL> self . _harmonize_columns ( parse_dates = parse_dates ) <EOL> if self . index is not None : <EOL> self . frame . set_index ( self . index , inplace = True ) <EOL> yield self . frame <EOL> def read ( self , coerce_float = True , parse_dates = None , columns = None , <EOL> chunksize = None ) : <EOL> if columns is not None and len ( columns ) > <NUM_LIT:0> : <EOL> from sqlalchemy import select <EOL> cols = [ self . table . c [ n ] for n in columns ] <EOL> if self . index is not None : <EOL> [ cols . insert ( <NUM_LIT:0> , self . table . c [ idx ] ) for idx in self . index [ : : - <NUM_LIT:1> ] ] <EOL> sql_select = select ( cols ) <EOL> else : <EOL> sql_select = self . table . select ( ) <EOL> result = self . pd_sql . execute ( sql_select ) <EOL> column_names = result . keys ( ) <EOL> if chunksize is not None : <EOL> return self . _query_iterator ( result , chunksize , column_names , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> else : <EOL> data = result . fetchall ( ) <EOL> self . frame = DataFrame . from_records ( <EOL> data , columns = column_names , coerce_float = coerce_float ) <EOL> self . _harmonize_columns ( parse_dates = parse_dates ) <EOL> if self . index is not None : <EOL> self . frame . set_index ( self . index , inplace = True ) <EOL> return self . frame <EOL> def _index_name ( self , index , index_label ) : <EOL> if index is True : <EOL> nlevels = self . frame . index . nlevels <EOL> if index_label is not None : <EOL> if not isinstance ( index_label , list ) : <EOL> index_label = [ index_label ] <EOL> if len ( index_label ) != nlevels : <EOL> raise ValueError ( <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" . format ( nlevels ) ) <EOL> else : <EOL> return index_label <EOL> if ( nlevels == <NUM_LIT:1> and '<STR_LIT:index>' not in self . frame . columns and <EOL> self . frame . index . name is None ) : <EOL> return [ '<STR_LIT:index>' ] <EOL> else : <EOL> return [ l if l is not None else "<STR_LIT>" . format ( i ) <EOL> for i , l in enumerate ( self . frame . index . names ) ] <EOL> elif isinstance ( index , string_types ) : <EOL> return [ index ] <EOL> elif isinstance ( index , list ) : <EOL> return index <EOL> else : <EOL> return None <EOL> def _get_column_names_and_types ( self , dtype_mapper ) : <EOL> column_names_and_types = [ ] <EOL> if self . index is not None : <EOL> for i , idx_label in enumerate ( self . index ) : <EOL> idx_type = dtype_mapper ( <EOL> self . frame . index . get_level_values ( i ) ) <EOL> column_names_and_types . append ( ( idx_label , idx_type , True ) ) <EOL> column_names_and_types += [ <EOL> ( text_type ( self . frame . columns [ i ] ) , <EOL> dtype_mapper ( self . frame . iloc [ : , i ] ) , <EOL> False ) <EOL> for i in range ( len ( self . frame . columns ) ) <EOL> ] <EOL> return column_names_and_types <EOL> def _create_table_setup ( self ) : <EOL> from sqlalchemy import Table , Column , PrimaryKeyConstraint <EOL> column_names_and_types = self . _get_column_names_and_types ( self . _sqlalchemy_type ) <EOL> columns = [ Column ( name , typ , index = is_index ) <EOL> for name , typ , is_index in column_names_and_types ] <EOL> if self . keys is not None : <EOL> if not com . is_list_like ( self . keys ) : <EOL> keys = [ self . keys ] <EOL> else : <EOL> keys = self . keys <EOL> pkc = PrimaryKeyConstraint ( * keys , name = self . name + '<STR_LIT>' ) <EOL> columns . append ( pkc ) <EOL> schema = self . schema or self . pd_sql . meta . schema <EOL> from sqlalchemy . schema import MetaData <EOL> meta = MetaData ( self . pd_sql , schema = schema ) <EOL> return Table ( self . name , meta , * columns , schema = schema ) <EOL> def _harmonize_columns ( self , parse_dates = None ) : <EOL> """<STR_LIT>""" <EOL> if parse_dates is True or parse_dates is None or parse_dates is False : <EOL> parse_dates = [ ] <EOL> if not hasattr ( parse_dates , '<STR_LIT>' ) : <EOL> parse_dates = [ parse_dates ] <EOL> for sql_col in self . table . columns : <EOL> col_name = sql_col . name <EOL> try : <EOL> df_col = self . frame [ col_name ] <EOL> col_type = self . _get_dtype ( sql_col . type ) <EOL> if ( col_type is datetime or col_type is date or <EOL> col_type is DatetimeTZDtype ) : <EOL> self . frame [ col_name ] = _handle_date_column ( df_col ) <EOL> elif col_type is float : <EOL> self . frame [ col_name ] = df_col . astype ( col_type , copy = False ) <EOL> elif len ( df_col ) == df_col . count ( ) : <EOL> if col_type is np . dtype ( '<STR_LIT>' ) or col_type is bool : <EOL> self . frame [ col_name ] = df_col . astype ( <EOL> col_type , copy = False ) <EOL> if col_name in parse_dates : <EOL> try : <EOL> fmt = parse_dates [ col_name ] <EOL> except TypeError : <EOL> fmt = None <EOL> self . frame [ col_name ] = _handle_date_column ( <EOL> df_col , format = fmt ) <EOL> except KeyError : <EOL> pass <EOL> def _get_notnull_col_dtype ( self , col ) : <EOL> """<STR_LIT>""" <EOL> col_for_inference = col <EOL> if col . dtype == '<STR_LIT:object>' : <EOL> notnulldata = col [ ~ isnull ( col ) ] <EOL> if len ( notnulldata ) : <EOL> col_for_inference = notnulldata <EOL> return lib . infer_dtype ( col_for_inference ) <EOL> def _sqlalchemy_type ( self , col ) : <EOL> dtype = self . dtype or { } <EOL> if col . name in dtype : <EOL> return self . dtype [ col . name ] <EOL> col_type = self . _get_notnull_col_dtype ( col ) <EOL> from sqlalchemy . types import ( BigInteger , Integer , Float , <EOL> Text , Boolean , <EOL> DateTime , Date , Time ) <EOL> if col_type == '<STR_LIT>' or col_type == '<STR_LIT>' : <EOL> try : <EOL> tz = col . tzinfo <EOL> return DateTime ( timezone = True ) <EOL> except : <EOL> return DateTime <EOL> if col_type == '<STR_LIT>' : <EOL> warnings . warn ( "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" , UserWarning , stacklevel = <NUM_LIT:8> ) <EOL> return BigInteger <EOL> elif col_type == '<STR_LIT>' : <EOL> if col . dtype == '<STR_LIT>' : <EOL> return Float ( precision = <NUM_LIT> ) <EOL> else : <EOL> return Float ( precision = <NUM_LIT> ) <EOL> elif col_type == '<STR_LIT>' : <EOL> if col . dtype == '<STR_LIT>' : <EOL> return Integer <EOL> else : <EOL> return BigInteger <EOL> elif col_type == '<STR_LIT>' : <EOL> return Boolean <EOL> elif col_type == '<STR_LIT:date>' : <EOL> return Date <EOL> elif col_type == '<STR_LIT:time>' : <EOL> return Time <EOL> elif col_type == '<STR_LIT>' : <EOL> raise ValueError ( '<STR_LIT>' ) <EOL> return Text <EOL> def _get_dtype ( self , sqltype ) : <EOL> from sqlalchemy . types import ( Integer , Float , Boolean , DateTime , <EOL> Date , TIMESTAMP ) <EOL> if isinstance ( sqltype , Float ) : <EOL> return float <EOL> elif isinstance ( sqltype , Integer ) : <EOL> return np . dtype ( '<STR_LIT>' ) <EOL> elif isinstance ( sqltype , TIMESTAMP ) : <EOL> if not sqltype . timezone : <EOL> return datetime <EOL> return DatetimeTZDtype <EOL> elif isinstance ( sqltype , DateTime ) : <EOL> return datetime <EOL> elif isinstance ( sqltype , Date ) : <EOL> return date <EOL> elif isinstance ( sqltype , Boolean ) : <EOL> return bool <EOL> return object <EOL> class PandasSQL ( PandasObject ) : <EOL> """<STR_LIT>""" <EOL> def read_sql ( self , * args , ** kwargs ) : <EOL> raise ValueError ( "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> def to_sql ( self , * args , ** kwargs ) : <EOL> raise ValueError ( "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> class SQLDatabase ( PandasSQL ) : <EOL> """<STR_LIT>""" <EOL> def __init__ ( self , engine , schema = None , meta = None ) : <EOL> self . connectable = engine <EOL> if not meta : <EOL> from sqlalchemy . schema import MetaData <EOL> meta = MetaData ( self . connectable , schema = schema ) <EOL> self . meta = meta <EOL> @ contextmanager <EOL> def run_transaction ( self ) : <EOL> with self . connectable . begin ( ) as tx : <EOL> if hasattr ( tx , '<STR_LIT>' ) : <EOL> yield tx <EOL> else : <EOL> yield self . connectable <EOL> def execute ( self , * args , ** kwargs ) : <EOL> """<STR_LIT>""" <EOL> return self . connectable . execute ( * args , ** kwargs ) <EOL> def read_table ( self , table_name , index_col = None , coerce_float = True , <EOL> parse_dates = None , columns = None , schema = None , <EOL> chunksize = None ) : <EOL> """<STR_LIT>""" <EOL> table = SQLTable ( table_name , self , index = index_col , schema = schema ) <EOL> return table . read ( coerce_float = coerce_float , <EOL> parse_dates = parse_dates , columns = columns , <EOL> chunksize = chunksize ) <EOL> @ staticmethod <EOL> def _query_iterator ( result , chunksize , columns , index_col = None , <EOL> coerce_float = True , parse_dates = None ) : <EOL> """<STR_LIT>""" <EOL> while True : <EOL> data = result . fetchmany ( chunksize ) <EOL> if not data : <EOL> break <EOL> else : <EOL> yield _wrap_result ( data , columns , index_col = index_col , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> def read_query ( self , sql , index_col = None , coerce_float = True , <EOL> parse_dates = None , params = None , chunksize = None ) : <EOL> """<STR_LIT>""" <EOL> args = _convert_params ( sql , params ) <EOL> result = self . execute ( * args ) <EOL> columns = result . keys ( ) <EOL> if chunksize is not None : <EOL> return self . _query_iterator ( result , chunksize , columns , <EOL> index_col = index_col , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> else : <EOL> data = result . fetchall ( ) <EOL> frame = _wrap_result ( data , columns , index_col = index_col , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> return frame <EOL> read_sql = read_query <EOL> def to_sql ( self , frame , name , if_exists = '<STR_LIT>' , index = True , <EOL> index_label = None , schema = None , chunksize = None , dtype = None ) : <EOL> """<STR_LIT>""" <EOL> if dtype is not None : <EOL> from sqlalchemy . types import to_instance , TypeEngine <EOL> for col , my_type in dtype . items ( ) : <EOL> if not isinstance ( to_instance ( my_type ) , TypeEngine ) : <EOL> raise ValueError ( '<STR_LIT>' <EOL> '<STR_LIT>' % col ) <EOL> table = SQLTable ( name , self , frame = frame , index = index , <EOL> if_exists = if_exists , index_label = index_label , <EOL> schema = schema , dtype = dtype ) <EOL> table . create ( ) <EOL> table . insert ( chunksize ) <EOL> if ( not name . isdigit ( ) and not name . islower ( ) ) : <EOL> engine = self . connectable . engine <EOL> with self . connectable . connect ( ) as conn : <EOL> table_names = engine . table_names ( <EOL> schema = schema or self . meta . schema , <EOL> connection = conn , <EOL> ) <EOL> if name not in table_names : <EOL> msg = ( <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> ) . format ( name ) <EOL> warnings . warn ( msg , UserWarning ) <EOL> @ property <EOL> def tables ( self ) : <EOL> return self . meta . tables <EOL> def has_table ( self , name , schema = None ) : <EOL> return self . connectable . run_callable ( <EOL> self . connectable . dialect . has_table , <EOL> name , <EOL> schema or self . meta . schema , <EOL> ) <EOL> def get_table ( self , table_name , schema = None ) : <EOL> schema = schema or self . meta . schema <EOL> if schema : <EOL> tbl = self . meta . tables . get ( '<STR_LIT:.>' . join ( [ schema , table_name ] ) ) <EOL> else : <EOL> tbl = self . meta . tables . get ( table_name ) <EOL> from sqlalchemy import Numeric <EOL> for column in tbl . columns : <EOL> if isinstance ( column . type , Numeric ) : <EOL> column . type . asdecimal = False <EOL> return tbl <EOL> def drop_table ( self , table_name , schema = None ) : <EOL> schema = schema or self . meta . schema <EOL> if self . has_table ( table_name , schema ) : <EOL> self . meta . reflect ( only = [ table_name ] , schema = schema ) <EOL> self . get_table ( table_name , schema ) . drop ( ) <EOL> self . meta . clear ( ) <EOL> def _create_sql_schema ( self , frame , table_name , keys = None , dtype = None ) : <EOL> table = SQLTable ( table_name , self , frame = frame , index = False , keys = keys , <EOL> dtype = dtype ) <EOL> return str ( table . sql_schema ( ) ) <EOL> _SQL_TYPES = { <EOL> '<STR_LIT:string>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } , <EOL> '<STR_LIT>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } , <EOL> '<STR_LIT>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } , <EOL> '<STR_LIT>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } , <EOL> '<STR_LIT:date>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } , <EOL> '<STR_LIT:time>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } , <EOL> '<STR_LIT>' : { <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> '<STR_LIT>' : '<STR_LIT>' , <EOL> } <EOL> } <EOL> def _get_unicode_name ( name ) : <EOL> try : <EOL> uname = name . encode ( "<STR_LIT:utf-8>" , "<STR_LIT:strict>" ) . decode ( "<STR_LIT:utf-8>" ) <EOL> except UnicodeError : <EOL> raise ValueError ( "<STR_LIT>" % name ) <EOL> return uname <EOL> def _get_valid_mysql_name ( name ) : <EOL> uname = _get_unicode_name ( name ) <EOL> if not len ( uname ) : <EOL> raise ValueError ( "<STR_LIT>" ) <EOL> basere = r'<STR_LIT>' <EOL> for c in uname : <EOL> if not re . match ( basere , c ) : <EOL> if not ( <NUM_LIT> < ord ( c ) < <NUM_LIT> ) : <EOL> raise ValueError ( "<STR_LIT>" % uname ) <EOL> return '<STR_LIT>' + uname + '<STR_LIT>' <EOL> def _get_valid_sqlite_name ( name ) : <EOL> uname = _get_unicode_name ( name ) <EOL> if not len ( uname ) : <EOL> raise ValueError ( "<STR_LIT>" ) <EOL> nul_index = uname . find ( "<STR_LIT:\x00>" ) <EOL> if nul_index >= <NUM_LIT:0> : <EOL> raise ValueError ( '<STR_LIT>' ) <EOL> return '<STR_LIT:">' + uname . replace ( '<STR_LIT:">' , '<STR_LIT>' ) + '<STR_LIT:">' <EOL> _SQL_WILDCARD = { <EOL> '<STR_LIT>' : '<STR_LIT:%s>' , <EOL> '<STR_LIT>' : '<STR_LIT:?>' <EOL> } <EOL> _SQL_GET_IDENTIFIER = { <EOL> '<STR_LIT>' : _get_valid_mysql_name , <EOL> '<STR_LIT>' : _get_valid_sqlite_name , <EOL> } <EOL> _SAFE_NAMES_WARNING = ( "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> class SQLiteTable ( SQLTable ) : <EOL> """<STR_LIT>""" <EOL> def __init__ ( self , * args , ** kwargs ) : <EOL> import sqlite3 <EOL> sqlite3 . register_adapter ( time , lambda _ : _ . strftime ( "<STR_LIT>" ) ) <EOL> super ( SQLiteTable , self ) . __init__ ( * args , ** kwargs ) <EOL> def sql_schema ( self ) : <EOL> return str ( "<STR_LIT>" . join ( self . table ) ) <EOL> def _execute_create ( self ) : <EOL> with self . pd_sql . run_transaction ( ) as conn : <EOL> for stmt in self . table : <EOL> conn . execute ( stmt ) <EOL> def insert_statement ( self ) : <EOL> names = list ( map ( text_type , self . frame . columns ) ) <EOL> flv = self . pd_sql . flavor <EOL> wld = _SQL_WILDCARD [ flv ] <EOL> escape = _SQL_GET_IDENTIFIER [ flv ] <EOL> if self . index is not None : <EOL> [ names . insert ( <NUM_LIT:0> , idx ) for idx in self . index [ : : - <NUM_LIT:1> ] ] <EOL> bracketed_names = [ escape ( column ) for column in names ] <EOL> col_names = '<STR_LIT:U+002C>' . join ( bracketed_names ) <EOL> wildcards = '<STR_LIT:U+002C>' . join ( [ wld ] * len ( names ) ) <EOL> insert_statement = '<STR_LIT>' % ( <EOL> escape ( self . name ) , col_names , wildcards ) <EOL> return insert_statement <EOL> def _execute_insert ( self , conn , keys , data_iter ) : <EOL> data_list = list ( data_iter ) <EOL> conn . executemany ( self . insert_statement ( ) , data_list ) <EOL> def _create_table_setup ( self ) : <EOL> """<STR_LIT>""" <EOL> column_names_and_types = self . _get_column_names_and_types ( self . _sql_type_name ) <EOL> pat = re . compile ( '<STR_LIT>' ) <EOL> column_names = [ col_name for col_name , _ , _ in column_names_and_types ] <EOL> if any ( map ( pat . search , column_names ) ) : <EOL> warnings . warn ( _SAFE_NAMES_WARNING , stacklevel = <NUM_LIT:6> ) <EOL> flv = self . pd_sql . flavor <EOL> escape = _SQL_GET_IDENTIFIER [ flv ] <EOL> create_tbl_stmts = [ escape ( cname ) + '<STR_LIT:U+0020>' + ctype <EOL> for cname , ctype , _ in column_names_and_types ] <EOL> if self . keys is not None and len ( self . keys ) : <EOL> if not com . is_list_like ( self . keys ) : <EOL> keys = [ self . keys ] <EOL> else : <EOL> keys = self . keys <EOL> cnames_br = "<STR_LIT:U+002CU+0020>" . join ( [ escape ( c ) for c in keys ] ) <EOL> create_tbl_stmts . append ( <EOL> "<STR_LIT>" . format ( <EOL> tbl = self . name , cnames_br = cnames_br ) ) <EOL> create_stmts = [ "<STR_LIT>" + escape ( self . name ) + "<STR_LIT>" + <EOL> '<STR_LIT>' . join ( create_tbl_stmts ) + "<STR_LIT>" ] <EOL> ix_cols = [ cname for cname , _ , is_index in column_names_and_types <EOL> if is_index ] <EOL> if len ( ix_cols ) : <EOL> cnames = "<STR_LIT:_>" . join ( ix_cols ) <EOL> cnames_br = "<STR_LIT:U+002C>" . join ( [ escape ( c ) for c in ix_cols ] ) <EOL> create_stmts . append ( <EOL> "<STR_LIT>" + escape ( "<STR_LIT>" + self . name + "<STR_LIT:_>" + cnames ) + <EOL> "<STR_LIT>" + escape ( self . name ) + "<STR_LIT>" + cnames_br + "<STR_LIT:)>" ) <EOL> return create_stmts <EOL> def _sql_type_name ( self , col ) : <EOL> dtype = self . dtype or { } <EOL> if col . name in dtype : <EOL> return dtype [ col . name ] <EOL> col_type = self . _get_notnull_col_dtype ( col ) <EOL> if col_type == '<STR_LIT>' : <EOL> warnings . warn ( "<STR_LIT>" <EOL> "<STR_LIT>" <EOL> "<STR_LIT>" , UserWarning , stacklevel = <NUM_LIT:8> ) <EOL> col_type = "<STR_LIT>" <EOL> elif col_type == "<STR_LIT>" : <EOL> col_type = "<STR_LIT>" <EOL> elif col_type == "<STR_LIT>" : <EOL> col_type = "<STR_LIT:string>" <EOL> elif col_type == "<STR_LIT>" : <EOL> raise ValueError ( '<STR_LIT>' ) <EOL> if col_type not in _SQL_TYPES : <EOL> col_type = "<STR_LIT:string>" <EOL> return _SQL_TYPES [ col_type ] [ self . pd_sql . flavor ] <EOL> class SQLiteDatabase ( PandasSQL ) : <EOL> """<STR_LIT>""" <EOL> def __init__ ( self , con , flavor , is_cursor = False ) : <EOL> self . is_cursor = is_cursor <EOL> self . con = con <EOL> if flavor is None : <EOL> flavor = '<STR_LIT>' <EOL> if flavor not in [ '<STR_LIT>' , '<STR_LIT>' ] : <EOL> raise NotImplementedError ( "<STR_LIT>" <EOL> "<STR_LIT>" ) <EOL> else : <EOL> self . flavor = flavor <EOL> @ contextmanager <EOL> def run_transaction ( self ) : <EOL> cur = self . con . cursor ( ) <EOL> try : <EOL> yield cur <EOL> self . con . commit ( ) <EOL> except : <EOL> self . con . rollback ( ) <EOL> raise <EOL> finally : <EOL> cur . close ( ) <EOL> def execute ( self , * args , ** kwargs ) : <EOL> if self . is_cursor : <EOL> cur = self . con <EOL> else : <EOL> cur = self . con . cursor ( ) <EOL> try : <EOL> if kwargs : <EOL> cur . execute ( * args , ** kwargs ) <EOL> else : <EOL> cur . execute ( * args ) <EOL> return cur <EOL> except Exception as exc : <EOL> try : <EOL> self . con . rollback ( ) <EOL> except Exception : <EOL> ex = DatabaseError ( "<STR_LIT>" <EOL> "<STR_LIT>" % ( args [ <NUM_LIT:0> ] , exc ) ) <EOL> raise_with_traceback ( ex ) <EOL> ex = DatabaseError ( <EOL> "<STR_LIT>" % ( args [ <NUM_LIT:0> ] , exc ) ) <EOL> raise_with_traceback ( ex ) <EOL> @ staticmethod <EOL> def _query_iterator ( cursor , chunksize , columns , index_col = None , <EOL> coerce_float = True , parse_dates = None ) : <EOL> """<STR_LIT>""" <EOL> while True : <EOL> data = cursor . fetchmany ( chunksize ) <EOL> if type ( data ) == tuple : <EOL> data = list ( data ) <EOL> if not data : <EOL> cursor . close ( ) <EOL> break <EOL> else : <EOL> yield _wrap_result ( data , columns , index_col = index_col , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> def read_query ( self , sql , index_col = None , coerce_float = True , params = None , <EOL> parse_dates = None , chunksize = None ) : <EOL> args = _convert_params ( sql , params ) <EOL> cursor = self . execute ( * args ) <EOL> columns = [ col_desc [ <NUM_LIT:0> ] for col_desc in cursor . description ] <EOL> if chunksize is not None : <EOL> return self . _query_iterator ( cursor , chunksize , columns , <EOL> index_col = index_col , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> else : <EOL> data = self . _fetchall_as_list ( cursor ) <EOL> cursor . close ( ) <EOL> frame = _wrap_result ( data , columns , index_col = index_col , <EOL> coerce_float = coerce_float , <EOL> parse_dates = parse_dates ) <EOL> return frame <EOL> def _fetchall_as_list ( self , cur ) : <EOL> result = cur . fetchall ( ) <EOL> if not isinstance ( result , list ) : <EOL> result = list ( result ) <EOL> return result <EOL> def to_sql ( self , frame , name , if_exists = '<STR_LIT>' , index = True , <EOL> index_label = None , schema = None , chunksize = None , dtype = None ) : <EOL> """<STR_LIT>""" <EOL> if dtype is not None : <EOL> for col , my_type in dtype . items ( ) : <EOL> if not isinstance ( my_type , str ) : <EOL> raise ValueError ( '<STR_LIT>' % ( <EOL> col , str ( my_type ) ) ) <EOL> table = SQLiteTable ( name , self , frame = frame , index = index , <EOL> if_exists = if_exists , index_label = index_label , <EOL> dtype = dtype ) <EOL> table . create ( ) <EOL> table . insert ( chunksize ) <EOL> def has_table ( self , name , schema = None ) : <EOL> wld = _SQL_WILDCARD [ self . flavor ] <EOL> flavor_map = { <EOL> '<STR_LIT>' : ( "<STR_LIT>" <EOL> "<STR_LIT>" ) % wld , <EOL> '<STR_LIT>' : "<STR_LIT>" % wld } <EOL> query = flavor_map . get ( self . flavor ) <EOL> return len ( self . execute ( query , [ name , ] ) . fetchall ( ) ) > <NUM_LIT:0> <EOL> def get_table ( self , table_name , schema = None ) : <EOL> return None <EOL> def drop_table ( self , name , schema = None ) : <EOL> escape = _SQL_GET_IDENTIFIER [ self . flavor ] <EOL> drop_sql = "<STR_LIT>" % escape ( name ) <EOL> self . execute ( drop_sql ) <EOL> def _create_sql_schema ( self , frame , table_name , keys = None , dtype = None ) : <EOL> table = SQLiteTable ( table_name , self , frame = frame , index = False , <EOL> keys = keys , dtype = dtype ) <EOL> return str ( table . sql_schema ( ) ) <EOL> def get_schema ( frame , name , flavor = '<STR_LIT>' , keys = None , con = None , dtype = None ) : <EOL> """<STR_LIT>""" <EOL> pandas_sql = pandasSQL_builder ( con = con , flavor = flavor ) <EOL> return pandas_sql . _create_sql_schema ( frame , name , keys = keys , dtype = dtype ) </s>
