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)