def to_sql(df, name, schema, con, index, if_exists, mode='default', **kwargs): """ Override the default `pandas.to_sql` method to allow for insertion of multiple rows of data at once. This is derived from the upstream patch at https://github.com/pandas-dev/pandas/pull/21401, and can be deprecated once it is merged and released in a new version of `pandas`. """ assert mode in ('default', 'multi'), 'unexpected `to_sql` mode {}'.format(mode) if mode == 'default': return df.to_sql(name=name, schema=schema, con=con, index=index, if_exists=if_exists, **kwargs) else: nrows = len(df) if nrows == 0: return chunksize = kwargs.get('chunksize', nrows) if chunksize == 0: raise ValueError('chunksize argument should be non-zero') chunks = int(nrows / chunksize) + 1 pd_sql = SQLDatabase(con) pd_table = SQLTable(name, pd_sql, frame=df, index=index, if_exists=if_exists, index_label=kwargs.get('insert_label'), schema=schema, dtype=kwargs.get('dtype')) pd_table.create() keys, data_list = pd_table.insert_data() with pd_sql.run_transaction() as conn: for i in range(chunks): start_i = i * chunksize end_i = min((i + 1) * chunksize, nrows) if start_i >= end_i: break chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list]) data = [{k: v for k, v in zip(keys, row)} for row in chunk_iter] conn.execute(pd_table.table.insert(data)) # multivalues insert
def _create_table(schema: str, table_name: str, creds: SqlCreds, df: pd.DataFrame, if_exists: str): """use pandas' own code to create the table and schema""" sql_db = SQLDatabase(engine=creds.engine, schema=schema) table = SQLTable( table_name, sql_db, frame=df, index=False, # already set as new col earlier if index=True if_exists=if_exists, index_label=None, schema=schema, dtype=None, ) table.create()
def to_sql_set_primary_key_and_not_null(self, frame, name, con, keys, sql_table, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None): # ref: https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L437 if if_exists not in ('fail', 'replace', 'append'): raise ValueError( "'{0}' is not valid for if_exists".format(if_exists)) # ref: https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L508 pandas_sql = SQLDatabase(con, schema=schema) if isinstance(frame, pd.Series): frame = frame.to_frame() elif not isinstance(frame, pd.DataFrame): raise NotImplementedError( "'frame' argument should be either a Series or a DataFrame") if dtype is not None: from sqlalchemy.types import to_instance, TypeEngine for col, my_type in dtype.items(): if not isinstance(to_instance(my_type), TypeEngine): raise ValueError( 'The type of {} is not a SQLAlchemy type '.format(col)) table = SQLTable(name, pandas_sql, frame=frame, index=index, if_exists=if_exists, index_label=index_label, schema=schema, keys=keys, dtype=dtype) table.table = sql_table table.create() table.insert(chunksize)
def to_sql(name, engine, frame, chunksize=None, **kwargs): table = SQLTable(name, engine, frame=frame, **kwargs) table.create() table.insert(chunksize)
def to_sql(name, engine, frame, chunksize=None, **kwargs): pandas_sql_engine = pandasSQL_builder(engine) table = SQLTable(name, pandas_sql_engine, frame=frame, **kwargs) table.create() table.insert(chunksize)
def to_sql(name, engine, frame, chunksize=None, **kwargs): table = SQLTable(name, engine, frame=frame, **kwargs) table.create() table.insert(chunksize)
def to_sql(name, engine, frame, chunksize=None, **kwargs): pandas_sql_engine = pandasSQL_builder(engine) table = SQLTable(name, pandas_sql_engine, frame=frame, **kwargs) table.create() table.insert(chunksize)