Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/pandas/io/sql.py: 17%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1"""
2Collection of query wrappers / abstractions to both facilitate data
3retrieval and to reduce dependency on DB-specific API.
4"""
6from __future__ import annotations
8from abc import (
9 ABC,
10 abstractmethod,
11)
12from contextlib import (
13 ExitStack,
14 contextmanager,
15)
16from datetime import (
17 date,
18 datetime,
19 time,
20)
21from functools import partial
22import re
23from typing import (
24 TYPE_CHECKING,
25 Any,
26 Literal,
27 Self,
28 cast,
29 overload,
30)
31import warnings
33import numpy as np
35from pandas._config import using_string_dtype
37from pandas._libs import lib
38from pandas.compat._optional import (
39 VERSIONS,
40 import_optional_dependency,
41)
42from pandas.errors import (
43 AbstractMethodError,
44 DatabaseError,
45)
46from pandas.util._decorators import set_module
47from pandas.util._exceptions import find_stack_level
48from pandas.util._validators import check_dtype_backend
50from pandas.core.dtypes.common import (
51 is_dict_like,
52 is_list_like,
53 is_object_dtype,
54 is_string_dtype,
55)
56from pandas.core.dtypes.dtypes import DatetimeTZDtype
57from pandas.core.dtypes.missing import isna
59from pandas import get_option
60from pandas.core.api import (
61 DataFrame,
62 Series,
63)
64from pandas.core.arrays import ArrowExtensionArray
65from pandas.core.arrays.string_ import StringDtype
66from pandas.core.base import PandasObject
67import pandas.core.common as com
68from pandas.core.common import maybe_make_list
69from pandas.core.internals.construction import convert_object_array
70from pandas.core.tools.datetimes import to_datetime
72from pandas.io._util import arrow_table_to_pandas
74if TYPE_CHECKING:
75 from collections.abc import (
76 Callable,
77 Generator,
78 Iterator,
79 Mapping,
80 )
82 from sqlalchemy import Table
83 from sqlalchemy.sql.expression import (
84 Delete,
85 Select,
86 TextClause,
87 )
89 from pandas._typing import (
90 DtypeArg,
91 DtypeBackend,
92 IndexLabel,
93 )
95 from pandas import Index
97# -----------------------------------------------------------------------------
98# -- Helper functions
101def _process_parse_dates_argument(parse_dates):
102 """Process parse_dates argument for read_sql functions"""
103 # handle non-list entries for parse_dates gracefully
104 if parse_dates is True or parse_dates is None or parse_dates is False:
105 parse_dates = []
107 elif not hasattr(parse_dates, "__iter__"):
108 parse_dates = [parse_dates]
109 return parse_dates
112def _handle_date_column(
113 col, utc: bool = False, format: str | dict[str, Any] | None = None
114):
115 if isinstance(format, dict):
116 # GH35185 Allow custom error values in parse_dates argument of
117 # read_sql like functions.
118 # Format can take on custom to_datetime argument values such as
119 # {"errors": "coerce"} or {"dayfirst": True}
120 return to_datetime(col, **format)
121 else:
122 # Allow passing of formatting string for integers
123 # GH17855
124 if format is None and (
125 issubclass(col.dtype.type, np.floating)
126 or issubclass(col.dtype.type, np.integer)
127 ):
128 format = "s"
129 if format in ["D", "d", "h", "m", "s", "ms", "us", "ns"]:
130 return to_datetime(col, errors="coerce", unit=format, utc=utc)
131 elif isinstance(col.dtype, DatetimeTZDtype):
132 # coerce to UTC timezone
133 # GH11216
134 return to_datetime(col, utc=True)
135 else:
136 return to_datetime(col, errors="coerce", format=format, utc=utc)
139def _parse_date_columns(data_frame: DataFrame, parse_dates) -> DataFrame:
140 """
141 Force non-datetime columns to be read as such.
142 Supports both string formatted and integer timestamp columns.
143 """
144 parse_dates = _process_parse_dates_argument(parse_dates)
146 # we want to coerce datetime64_tz dtypes for now to UTC
147 # we could in theory do a 'nice' conversion from a FixedOffset tz
148 # GH11216
149 for i, (col_name, df_col) in enumerate(data_frame.items()):
150 if isinstance(df_col.dtype, DatetimeTZDtype) or col_name in parse_dates:
151 try:
152 fmt = parse_dates[col_name]
153 except (KeyError, TypeError):
154 fmt = None
155 data_frame.isetitem(i, _handle_date_column(df_col, format=fmt))
157 return data_frame
160def _convert_arrays_to_dataframe(
161 data,
162 columns,
163 coerce_float: bool = True,
164 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
165) -> DataFrame:
166 content = lib.to_object_array_tuples(data)
167 idx_len = content.shape[0]
168 arrays = convert_object_array(
169 list(content.T),
170 dtype=None,
171 coerce_float=coerce_float,
172 dtype_backend=dtype_backend,
173 )
174 if dtype_backend == "pyarrow":
175 pa = import_optional_dependency("pyarrow")
177 result_arrays = []
178 for arr in arrays:
179 pa_array = pa.array(arr, from_pandas=True)
180 if arr.dtype == "string":
181 # TODO: Arrow still infers strings arrays as regular strings instead
182 # of large_string, which is what we preserver everywhere else for
183 # dtype_backend="pyarrow". We may want to reconsider this
184 pa_array = pa_array.cast(pa.string())
185 result_arrays.append(ArrowExtensionArray(pa_array))
186 arrays = result_arrays # type: ignore[assignment]
187 if arrays:
188 return DataFrame._from_arrays(
189 arrays, columns=columns, index=range(idx_len), verify_integrity=False
190 )
191 else:
192 return DataFrame(columns=columns)
195def _wrap_result(
196 data,
197 columns,
198 index_col=None,
199 coerce_float: bool = True,
200 parse_dates=None,
201 dtype: DtypeArg | None = None,
202 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
203) -> DataFrame:
204 """Wrap result set of a SQLAlchemy query in a DataFrame."""
205 frame = _convert_arrays_to_dataframe(data, columns, coerce_float, dtype_backend)
207 if dtype:
208 frame = frame.astype(dtype)
210 frame = _parse_date_columns(frame, parse_dates)
212 if index_col is not None:
213 frame = frame.set_index(index_col)
215 return frame
218def _wrap_result_adbc(
219 df: DataFrame,
220 *,
221 index_col=None,
222 parse_dates=None,
223 dtype: DtypeArg | None = None,
224 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
225) -> DataFrame:
226 """Wrap result set of a SQLAlchemy query in a DataFrame."""
227 if dtype:
228 df = df.astype(dtype)
230 df = _parse_date_columns(df, parse_dates)
232 if index_col is not None:
233 df = df.set_index(index_col)
235 return df
238# -----------------------------------------------------------------------------
239# -- Read and write to DataFrames
242@overload
243def read_sql_table( # pyright: ignore[reportOverlappingOverload]
244 table_name: str,
245 con,
246 schema=...,
247 index_col: str | list[str] | None = ...,
248 coerce_float=...,
249 parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
250 columns: list[str] | None = ...,
251 chunksize: None = ...,
252 dtype_backend: DtypeBackend | lib.NoDefault = ...,
253) -> DataFrame: ...
256@overload
257def read_sql_table(
258 table_name: str,
259 con,
260 schema=...,
261 index_col: str | list[str] | None = ...,
262 coerce_float=...,
263 parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
264 columns: list[str] | None = ...,
265 chunksize: int = ...,
266 dtype_backend: DtypeBackend | lib.NoDefault = ...,
267) -> Iterator[DataFrame]: ...
270@set_module("pandas")
271def read_sql_table(
272 table_name: str,
273 con,
274 schema: str | None = None,
275 index_col: str | list[str] | None = None,
276 coerce_float: bool = True,
277 parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = None,
278 columns: list[str] | None = None,
279 chunksize: int | None = None,
280 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
281) -> DataFrame | Iterator[DataFrame]:
282 """
283 Read SQL database table into a DataFrame.
285 Given a table name and a SQLAlchemy connectable, returns a DataFrame.
286 This function does not support DBAPI connections.
288 Parameters
289 ----------
290 table_name : str
291 Name of SQL table in database.
292 con : SQLAlchemy connectable or str
293 A database URI could be provided as str.
294 SQLite DBAPI connection mode not supported.
295 schema : str, default None
296 Name of SQL schema in database to query (if database flavor
297 supports this). Uses default schema if None (default).
298 index_col : str or list of str, optional, default: None
299 Column(s) to set as index(MultiIndex).
300 coerce_float : bool, default True
301 Attempts to convert values of non-string, non-numeric objects (like
302 decimal.Decimal) to floating point. Can result in loss of Precision.
303 parse_dates : list or dict, default None
304 - List of column names to parse as dates.
305 - Dict of ``{column_name: format string}`` where format string is
306 strftime compatible in case of parsing string times or is one of
307 (D, s, ns, ms, us) in case of parsing integer timestamps.
308 - Dict of ``{column_name: arg dict}``, where the arg dict corresponds
309 to the keyword arguments of :func:`pandas.to_datetime`
310 Especially useful with databases without native Datetime support,
311 such as SQLite.
312 columns : list, default None
313 List of column names to select from SQL table.
314 chunksize : int, default None
315 If specified, returns an iterator where `chunksize` is the number of
316 rows to include in each chunk.
317 dtype_backend : {'numpy_nullable', 'pyarrow'}
318 Back-end data type applied to the resultant :class:`DataFrame`
319 (still experimental). If not specified, the default behavior
320 is to not use nullable data types. If specified, the behavior
321 is as follows:
323 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
324 * ``"pyarrow"``: returns pyarrow-backed nullable
325 :class:`ArrowDtype` :class:`DataFrame`
327 .. versionadded:: 2.0
329 Returns
330 -------
331 DataFrame or Iterator[DataFrame]
332 A SQL table is returned as two-dimensional data structure with labeled
333 axes.
335 See Also
336 --------
337 read_sql_query : Read SQL query into a DataFrame.
338 read_sql : Read SQL query or database table into a DataFrame.
340 Notes
341 -----
342 Any datetime values with time zone information will be converted to UTC.
344 Examples
345 --------
346 >>> pd.read_sql_table("table_name", "postgres:///db_name") # doctest:+SKIP
347 """
349 check_dtype_backend(dtype_backend)
350 if dtype_backend is lib.no_default:
351 dtype_backend = "numpy" # type: ignore[assignment]
352 assert dtype_backend is not lib.no_default
354 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
355 if not pandas_sql.has_table(table_name):
356 raise ValueError(f"Table {table_name} not found")
358 table = pandas_sql.read_table(
359 table_name,
360 index_col=index_col,
361 coerce_float=coerce_float,
362 parse_dates=parse_dates,
363 columns=columns,
364 chunksize=chunksize,
365 dtype_backend=dtype_backend,
366 )
368 if table is not None:
369 return table
370 else:
371 raise ValueError(f"Table {table_name} not found", con)
374@overload
375def read_sql_query( # pyright: ignore[reportOverlappingOverload]
376 sql,
377 con,
378 index_col: str | list[str] | None = ...,
379 coerce_float=...,
380 params: list[Any] | Mapping[str, Any] | None = ...,
381 parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
382 chunksize: None = ...,
383 dtype: DtypeArg | None = ...,
384 dtype_backend: DtypeBackend | lib.NoDefault = ...,
385) -> DataFrame: ...
388@overload
389def read_sql_query(
390 sql,
391 con,
392 index_col: str | list[str] | None = ...,
393 coerce_float=...,
394 params: list[Any] | Mapping[str, Any] | None = ...,
395 parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = ...,
396 chunksize: int = ...,
397 dtype: DtypeArg | None = ...,
398 dtype_backend: DtypeBackend | lib.NoDefault = ...,
399) -> Iterator[DataFrame]: ...
402@set_module("pandas")
403def read_sql_query(
404 sql,
405 con,
406 index_col: str | list[str] | None = None,
407 coerce_float: bool = True,
408 params: list[Any] | Mapping[str, Any] | None = None,
409 parse_dates: list[str] | dict[str, str] | dict[str, dict[str, Any]] | None = None,
410 chunksize: int | None = None,
411 dtype: DtypeArg | None = None,
412 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
413) -> DataFrame | Iterator[DataFrame]:
414 """
415 Read SQL query into a DataFrame.
417 Returns a DataFrame corresponding to the result set of the query
418 string. Optionally provide an `index_col` parameter to use one of the
419 columns as the index, otherwise default integer index will be used.
421 Parameters
422 ----------
423 sql : str SQL query or SQLAlchemy Selectable (select or text object)
424 SQL query to be executed.
425 con : SQLAlchemy connectable, str, or sqlite3 connection
426 Using SQLAlchemy makes it possible to use any DB supported by that
427 library. If a DBAPI2 object, only sqlite3 is supported.
428 index_col : str or list of str, optional, default: None
429 Column(s) to set as index(MultiIndex).
430 coerce_float : bool, default True
431 Attempts to convert values of non-string, non-numeric objects (like
432 decimal.Decimal) to floating point. Useful for SQL result sets.
433 params : list, tuple or mapping, optional, default: None
434 List of parameters to pass to execute method. The syntax used
435 to pass parameters is database driver dependent. Check your
436 database driver documentation for which of the five syntax styles,
437 described in PEP 249's paramstyle, is supported.
438 Eg. for psycopg2, uses %(name)s so use params={'name' : 'value'}.
439 parse_dates : list or dict, default: None
440 - List of column names to parse as dates.
441 - Dict of ``{column_name: format string}`` where format string is
442 strftime compatible in case of parsing string times, or is one of
443 (D, s, ns, ms, us) in case of parsing integer timestamps.
444 - Dict of ``{column_name: arg dict}``, where the arg dict corresponds
445 to the keyword arguments of :func:`pandas.to_datetime`
446 Especially useful with databases without native Datetime support,
447 such as SQLite.
448 chunksize : int, default None
449 If specified, return an iterator where `chunksize` is the number of
450 rows to include in each chunk.
451 dtype : Type name or dict of columns
452 Data type for data or columns. E.g. np.float64 or
453 {'a': np.float64, 'b': np.int32, 'c': 'Int64'}.
454 dtype_backend : {'numpy_nullable', 'pyarrow'}
455 Back-end data type applied to the resultant :class:`DataFrame`
456 (still experimental). If not specified, the default behavior
457 is to not use nullable data types. If specified, the behavior
458 is as follows:
460 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
461 * ``"pyarrow"``: returns pyarrow-backed nullable
462 :class:`ArrowDtype` :class:`DataFrame`
464 .. versionadded:: 2.0
466 Returns
467 -------
468 DataFrame or Iterator[DataFrame]
469 Returns a DataFrame object that contains the result set of the
470 executed SQL query, in relation to the specified database connection.
472 See Also
473 --------
474 read_sql_table : Read SQL database table into a DataFrame.
475 read_sql : Read SQL query or database table into a DataFrame.
477 Notes
478 -----
479 Any datetime values with time zone information parsed via the `parse_dates`
480 parameter will be converted to UTC.
482 Examples
483 --------
484 >>> from sqlalchemy import create_engine # doctest: +SKIP
485 >>> engine = create_engine("sqlite:///database.db") # doctest: +SKIP
486 >>> sql_query = "SELECT int_column FROM test_data" # doctest: +SKIP
487 >>> with engine.connect() as conn, conn.begin(): # doctest: +SKIP
488 ... data = pd.read_sql_query(sql_query, conn) # doctest: +SKIP
489 """
491 check_dtype_backend(dtype_backend)
492 if dtype_backend is lib.no_default:
493 dtype_backend = "numpy" # type: ignore[assignment]
494 assert dtype_backend is not lib.no_default
496 with pandasSQL_builder(con) as pandas_sql:
497 return pandas_sql.read_query(
498 sql,
499 index_col=index_col,
500 params=params,
501 coerce_float=coerce_float,
502 parse_dates=parse_dates,
503 chunksize=chunksize,
504 dtype=dtype,
505 dtype_backend=dtype_backend,
506 )
509@overload
510def read_sql( # pyright: ignore[reportOverlappingOverload]
511 sql,
512 con,
513 index_col: str | list[str] | None = ...,
514 coerce_float=...,
515 params=...,
516 parse_dates=...,
517 columns: list[str] = ...,
518 chunksize: None = ...,
519 dtype_backend: DtypeBackend | lib.NoDefault = ...,
520 dtype: DtypeArg | None = None,
521) -> DataFrame: ...
524@overload
525def read_sql(
526 sql,
527 con,
528 index_col: str | list[str] | None = ...,
529 coerce_float=...,
530 params=...,
531 parse_dates=...,
532 columns: list[str] = ...,
533 chunksize: int = ...,
534 dtype_backend: DtypeBackend | lib.NoDefault = ...,
535 dtype: DtypeArg | None = None,
536) -> Iterator[DataFrame]: ...
539@set_module("pandas")
540def read_sql(
541 sql,
542 con,
543 index_col: str | list[str] | None = None,
544 coerce_float: bool = True,
545 params=None,
546 parse_dates=None,
547 columns: list[str] | None = None,
548 chunksize: int | None = None,
549 dtype_backend: DtypeBackend | lib.NoDefault = lib.no_default,
550 dtype: DtypeArg | None = None,
551) -> DataFrame | Iterator[DataFrame]:
552 """
553 Read SQL query or database table into a DataFrame.
555 This function is a convenience wrapper around ``read_sql_table`` and
556 ``read_sql_query`` (for backward compatibility). It will delegate
557 to the specific function depending on the provided input. A SQL query
558 will be routed to ``read_sql_query``, while a database table name will
559 be routed to ``read_sql_table``. Note that the delegated function might
560 have more specific notes about their functionality not listed here.
562 Parameters
563 ----------
564 sql : str or SQLAlchemy Selectable (select or text object)
565 SQL query to be executed or a table name.
566 con : ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection
567 ADBC provides high performance I/O with native type support, where available.
568 Using SQLAlchemy makes it possible to use any DB supported by that
569 library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible
570 for engine disposal and connection closure for the ADBC connection and
571 SQLAlchemy connectable; str connections are closed automatically. See
572 `here <https://docs.sqlalchemy.org/en/20/core/connections.html>`_.
573 index_col : str or list of str, optional, default: None
574 Column(s) to set as index(MultiIndex).
575 coerce_float : bool, default True
576 Attempts to convert values of non-string, non-numeric objects (like
577 decimal.Decimal) to floating point, useful for SQL result sets.
578 params : list, tuple or dict, optional, default: None
579 List of parameters to pass to execute method. The syntax used
580 to pass parameters is database driver dependent. Check your
581 database driver documentation for which of the five syntax styles,
582 described in PEP 249's paramstyle, is supported.
583 Eg. for psycopg2, uses %(name)s so use params={'name' : 'value'}.
584 parse_dates : list or dict, default: None
585 - List of column names to parse as dates.
586 - Dict of ``{column_name: format string}`` where format string is
587 strftime compatible in case of parsing string times, or is one of
588 (D, s, ns, ms, us) in case of parsing integer timestamps.
589 - Dict of ``{column_name: arg dict}``, where the arg dict corresponds
590 to the keyword arguments of :func:`pandas.to_datetime`
591 Especially useful with databases without native Datetime support,
592 such as SQLite.
593 columns : list, default: None
594 List of column names to select from SQL table (only used when reading
595 a table).
596 chunksize : int, default None
597 If specified, return an iterator where `chunksize` is the
598 number of rows to include in each chunk.
599 dtype_backend : {'numpy_nullable', 'pyarrow'}
600 Back-end data type applied to the resultant :class:`DataFrame`
601 (still experimental). If not specified, the default behavior
602 is to not use nullable data types. If specified, the behavior
603 is as follows:
605 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
606 * ``"pyarrow"``: returns pyarrow-backed nullable
607 :class:`ArrowDtype` :class:`DataFrame`
609 .. versionadded:: 2.0
610 dtype : Type name or dict of columns
611 Data type for data or columns. E.g. np.float64 or
612 {'a': np.float64, 'b': np.int32, 'c': 'Int64'}.
613 The argument is ignored if a table is passed instead of a query.
615 .. versionadded:: 2.0.0
617 Returns
618 -------
619 DataFrame or Iterator[DataFrame]
620 Returns a DataFrame object that contains the result set of the
621 executed SQL query or an SQL Table based on the provided input,
622 in relation to the specified database connection.
624 See Also
625 --------
626 read_sql_table : Read SQL database table into a DataFrame.
627 read_sql_query : Read SQL query into a DataFrame.
629 Notes
630 -----
631 ``pandas`` does not attempt to sanitize SQL statements;
632 instead it simply forwards the statement you are executing
633 to the underlying driver, which may or may not sanitize from there.
634 Please refer to the underlying driver documentation for any details.
635 Generally, be wary when accepting statements from arbitrary sources.
637 Examples
638 --------
639 Read data from SQL via either a SQL query or a SQL tablename.
640 When using a SQLite database only SQL queries are accepted,
641 providing only the SQL tablename will result in an error.
643 >>> from sqlite3 import connect
644 >>> conn = connect(":memory:")
645 >>> df = pd.DataFrame(
646 ... data=[[0, "10/11/12"], [1, "12/11/10"]],
647 ... columns=["int_column", "date_column"],
648 ... )
649 >>> df.to_sql(name="test_data", con=conn)
650 2
652 >>> pd.read_sql("SELECT int_column, date_column FROM test_data", conn)
653 int_column date_column
654 0 0 10/11/12
655 1 1 12/11/10
657 >>> pd.read_sql("test_data", "postgres:///db_name") # doctest:+SKIP
659 For parameterized query, using ``params`` is recommended over string interpolation.
661 >>> from sqlalchemy import text
662 >>> sql = text(
663 ... "SELECT int_column, date_column FROM test_data WHERE int_column=:int_val"
664 ... )
665 >>> pd.read_sql(sql, conn, params={"int_val": 1}) # doctest:+SKIP
666 int_column date_column
667 0 1 12/11/10
669 Apply date parsing to columns through the ``parse_dates`` argument
670 The ``parse_dates`` argument calls ``pd.to_datetime`` on the provided columns.
671 Custom argument values for applying ``pd.to_datetime`` on a column are specified
672 via a dictionary format:
674 >>> pd.read_sql(
675 ... "SELECT int_column, date_column FROM test_data",
676 ... conn,
677 ... parse_dates={"date_column": {"format": "%d/%m/%y"}},
678 ... )
679 int_column date_column
680 0 0 2012-11-10
681 1 1 2010-11-12
683 .. versionadded:: 2.2.0
685 pandas now supports reading via ADBC drivers
687 >>> from adbc_driver_postgresql import dbapi # doctest:+SKIP
688 >>> with dbapi.connect("postgres:///db_name") as conn: # doctest:+SKIP
689 ... pd.read_sql("SELECT int_column FROM test_data", conn)
690 int_column
691 0 0
692 1 1
693 """
695 check_dtype_backend(dtype_backend)
696 if dtype_backend is lib.no_default:
697 dtype_backend = "numpy" # type: ignore[assignment]
698 assert dtype_backend is not lib.no_default
700 with pandasSQL_builder(con) as pandas_sql:
701 if isinstance(pandas_sql, SQLiteDatabase):
702 return pandas_sql.read_query(
703 sql,
704 index_col=index_col,
705 params=params,
706 coerce_float=coerce_float,
707 parse_dates=parse_dates,
708 chunksize=chunksize,
709 dtype_backend=dtype_backend,
710 dtype=dtype,
711 )
713 try:
714 _is_table_name = pandas_sql.has_table(sql)
715 except Exception:
716 # using generic exception to catch errors from sql drivers (GH24988)
717 _is_table_name = False
719 if _is_table_name:
720 return pandas_sql.read_table(
721 sql,
722 index_col=index_col,
723 coerce_float=coerce_float,
724 parse_dates=parse_dates,
725 columns=columns,
726 chunksize=chunksize,
727 dtype_backend=dtype_backend,
728 )
729 else:
730 return pandas_sql.read_query(
731 sql,
732 index_col=index_col,
733 params=params,
734 coerce_float=coerce_float,
735 parse_dates=parse_dates,
736 chunksize=chunksize,
737 dtype_backend=dtype_backend,
738 dtype=dtype,
739 )
742def to_sql(
743 frame,
744 name: str,
745 con,
746 schema: str | None = None,
747 if_exists: Literal["fail", "replace", "append", "delete_rows"] = "fail",
748 index: bool = True,
749 index_label: IndexLabel | None = None,
750 chunksize: int | None = None,
751 dtype: DtypeArg | None = None,
752 method: Literal["multi"] | Callable | None = None,
753 engine: str = "auto",
754 **engine_kwargs,
755) -> int | None:
756 """
757 Write records stored in a DataFrame to a SQL database.
759 .. warning::
760 The pandas library does not attempt to sanitize inputs provided via a to_sql call.
761 Please refer to the documentation for the underlying database driver to see if it
762 will properly prevent injection, or alternatively be advised of a security risk when
763 executing arbitrary commands in a to_sql call.
765 Parameters
766 ----------
767 frame : DataFrame, Series
768 name : str
769 Name of SQL table.
770 con : ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection
771 or sqlite3 DBAPI2 connection
772 ADBC provides high performance I/O with native type support, where available.
773 Using SQLAlchemy makes it possible to use any DB supported by that
774 library.
775 If a DBAPI2 object, only sqlite3 is supported.
776 schema : str, optional
777 Name of SQL schema in database to write to (if database flavor
778 supports this). If None, use default schema (default).
779 if_exists : {'fail', 'replace', 'append', 'delete_rows'}, default 'fail'
780 - fail: If table exists, do nothing.
781 - replace: If table exists, drop it, recreate it, and insert data.
782 - append: If table exists, insert data. Create if does not exist.
783 - delete_rows: If a table exists, delete all records and insert data.
784 index : bool, default True
785 Write DataFrame index as a column.
786 index_label : str or sequence, optional
787 Column label for index column(s). If None is given (default) and
788 `index` is True, then the index names are used.
789 A sequence should be given if the DataFrame uses MultiIndex.
790 chunksize : int, optional
791 Specify the number of rows in each batch to be written at a time.
792 By default, all rows will be written at once.
793 dtype : dict or scalar, optional
794 Specifying the datatype for columns. If a dictionary is used, the
795 keys should be the column names and the values should be the
796 SQLAlchemy types or strings for the sqlite3 fallback mode. If a
797 scalar is provided, it will be applied to all columns.
798 method : {None, 'multi', callable}, optional
799 Controls the SQL insertion clause used:
801 - None : Uses standard SQL ``INSERT`` clause (one per row).
802 - ``'multi'``: Pass multiple values in a single ``INSERT`` clause.
803 - callable with signature ``(pd_table, conn, keys, data_iter) -> int | None``.
805 Details and a sample callable implementation can be found in the
806 section :ref:`insert method <io.sql.method>`.
807 engine : {'auto', 'sqlalchemy'}, default 'auto'
808 SQL engine library to use. If 'auto', then the option
809 ``io.sql.engine`` is used. The default ``io.sql.engine``
810 behavior is 'sqlalchemy'
812 **engine_kwargs
813 Any additional kwargs are passed to the engine.
815 Returns
816 -------
817 None or int
818 Number of rows affected by to_sql. None is returned if the callable
819 passed into ``method`` does not return an integer number of rows.
821 Notes
822 -----
823 The returned rows affected is the sum of the ``rowcount`` attribute of ``sqlite3.Cursor``
824 or SQLAlchemy connectable. If using ADBC the returned rows are the result
825 of ``Cursor.adbc_ingest``. The returned value may not reflect the exact number of written
826 rows as stipulated in the
827 `sqlite3 <https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.rowcount>`__ or
828 `SQLAlchemy <https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.BaseCursorResult.rowcount>`__
829 """ # noqa: E501
830 if if_exists not in ("fail", "replace", "append", "delete_rows"):
831 raise ValueError(f"'{if_exists}' is not valid for if_exists")
833 if isinstance(frame, Series):
834 frame = frame.to_frame()
835 elif not isinstance(frame, DataFrame):
836 raise NotImplementedError(
837 "'frame' argument should be either a Series or a DataFrame"
838 )
840 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
841 return pandas_sql.to_sql(
842 frame,
843 name,
844 if_exists=if_exists,
845 index=index,
846 index_label=index_label,
847 schema=schema,
848 chunksize=chunksize,
849 dtype=dtype,
850 method=method,
851 engine=engine,
852 **engine_kwargs,
853 )
856def has_table(table_name: str, con, schema: str | None = None) -> bool:
857 """
858 Check if DataBase has named table.
860 Parameters
861 ----------
862 table_name: string
863 Name of SQL table.
864 con: ADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection
865 ADBC provides high performance I/O with native type support, where available.
866 Using SQLAlchemy makes it possible to use any DB supported by that
867 library.
868 If a DBAPI2 object, only sqlite3 is supported.
869 schema : string, default None
870 Name of SQL schema in database to write to (if database flavor supports
871 this). If None, use default schema (default).
873 Returns
874 -------
875 boolean
876 """
877 with pandasSQL_builder(con, schema=schema) as pandas_sql:
878 return pandas_sql.has_table(table_name)
881table_exists = has_table
884def pandasSQL_builder(
885 con,
886 schema: str | None = None,
887 need_transaction: bool = False,
888) -> PandasSQL:
889 """
890 Convenience function to return the correct PandasSQL subclass based on the
891 provided parameters. Also creates a sqlalchemy connection and transaction
892 if necessary.
893 """
894 import sqlite3
896 if isinstance(con, sqlite3.Connection) or con is None:
897 return SQLiteDatabase(con)
899 sqlalchemy = import_optional_dependency("sqlalchemy", errors="ignore")
901 if isinstance(con, str) and sqlalchemy is None:
902 raise ImportError(
903 f"Using URI string without version '{VERSIONS['sqlalchemy']}' or newer "
904 "of 'sqlalchemy' installed."
905 )
907 if sqlalchemy is not None and isinstance(con, (str, sqlalchemy.engine.Connectable)):
908 return SQLDatabase(con, schema, need_transaction)
910 adbc = import_optional_dependency("adbc_driver_manager.dbapi", errors="ignore")
911 if adbc and isinstance(con, adbc.Connection):
912 return ADBCDatabase(con)
914 warnings.warn(
915 "pandas only supports SQLAlchemy connectable (engine/connection) or "
916 "database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 "
917 "objects are not tested. Please consider using SQLAlchemy.",
918 UserWarning,
919 stacklevel=find_stack_level(),
920 )
921 return SQLiteDatabase(con)
924class SQLTable(PandasObject):
925 """
926 For mapping Pandas tables to SQL tables.
927 Uses fact that table is reflected by SQLAlchemy to
928 do better type conversions.
929 Also holds various flags needed to avoid having to
930 pass them between functions all the time.
931 """
933 # TODO: support for multiIndex
935 def __init__(
936 self,
937 name: str,
938 pandas_sql_engine,
939 frame=None,
940 index: bool | str | list[str] | None = True,
941 if_exists: Literal["fail", "replace", "append", "delete_rows"] = "fail",
942 prefix: str = "pandas",
943 index_label=None,
944 schema=None,
945 keys=None,
946 dtype: DtypeArg | None = None,
947 ) -> None:
948 self.name = name
949 self.pd_sql = pandas_sql_engine
950 self.prefix = prefix
951 self.frame = frame
952 self.index = self._index_name(index, index_label)
953 self.schema = schema
954 self.if_exists = if_exists
955 self.keys = keys
956 self.dtype = dtype
958 if frame is not None:
959 # We want to initialize based on a dataframe
960 self.table = self._create_table_setup()
961 else:
962 # no data provided, read-only mode
963 self.table = self.pd_sql.get_table(self.name, self.schema)
965 if self.table is None:
966 raise ValueError(f"Could not init table '{name}'")
968 if not len(self.name):
969 raise ValueError("Empty table name specified")
971 def exists(self):
972 return self.pd_sql.has_table(self.name, self.schema)
974 def sql_schema(self) -> str:
975 from sqlalchemy.schema import CreateTable
977 return str(CreateTable(self.table).compile(self.pd_sql.con))
979 def _execute_create(self) -> None:
980 # Inserting table into database, add to MetaData object
981 self.table = self.table.to_metadata(self.pd_sql.meta)
982 with self.pd_sql.run_transaction():
983 self.table.create(bind=self.pd_sql.con)
985 def create(self) -> None:
986 if self.exists():
987 if self.if_exists == "fail":
988 raise ValueError(f"Table '{self.name}' already exists.")
989 elif self.if_exists == "replace":
990 self.pd_sql.drop_table(self.name, self.schema)
991 self._execute_create()
992 elif self.if_exists == "append":
993 pass
994 elif self.if_exists == "delete_rows":
995 self.pd_sql.delete_rows(self.name, self.schema)
996 else:
997 raise ValueError(f"'{self.if_exists}' is not valid for if_exists")
998 else:
999 self._execute_create()
1001 def _execute_insert(self, conn, keys: list[str], data_iter) -> int:
1002 """
1003 Execute SQL statement inserting data
1005 Parameters
1006 ----------
1007 conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection
1008 keys : list of str
1009 Column names
1010 data_iter : generator of list
1011 Each item contains a list of values to be inserted
1012 """
1013 data = [dict(zip(keys, row, strict=True)) for row in data_iter]
1014 result = self.pd_sql.execute(self.table.insert(), data)
1015 return result.rowcount
1017 def _execute_insert_multi(self, conn, keys: list[str], data_iter) -> int:
1018 """
1019 Alternative to _execute_insert for DBs support multi-value INSERT.
1021 Note: multi-value insert is usually faster for analytics DBs
1022 and tables containing a few columns
1023 but performance degrades quickly with increase of columns.
1025 """
1027 from sqlalchemy import insert
1029 data = [dict(zip(keys, row, strict=True)) for row in data_iter]
1030 stmt = insert(self.table).values(data)
1031 result = self.pd_sql.execute(stmt)
1032 return result.rowcount
1034 def insert_data(self) -> tuple[list[str], list[np.ndarray]]:
1035 if self.index is not None:
1036 temp = self.frame.copy(deep=False)
1037 temp.index.names = self.index
1038 try:
1039 temp.reset_index(inplace=True)
1040 except ValueError as err:
1041 raise ValueError(f"duplicate name in index/columns: {err}") from err
1042 else:
1043 temp = self.frame
1045 column_names = list(map(str, temp.columns))
1046 ncols = len(column_names)
1047 # this just pre-allocates the list: None's will be replaced with ndarrays
1048 # error: List item 0 has incompatible type "None"; expected "ndarray"
1049 data_list: list[np.ndarray] = [None] * ncols # type: ignore[list-item]
1051 for i, (_, ser) in enumerate(temp.items()):
1052 if ser.dtype.kind == "M":
1053 if isinstance(ser._values, ArrowExtensionArray):
1054 import pyarrow as pa
1056 if pa.types.is_date(ser.dtype.pyarrow_dtype):
1057 # GH#53854 to_pydatetime not supported for pyarrow date dtypes
1058 d = ser._values.to_numpy(dtype=object)
1059 else:
1060 d = ser.dt.to_pydatetime()._values
1061 else:
1062 d = ser._values.to_pydatetime()
1063 elif ser.dtype.kind == "m":
1064 vals = ser._values
1065 if isinstance(vals, ArrowExtensionArray):
1066 vals = vals.to_numpy(dtype=np.dtype("m8[ns]"))
1067 # store as integers, see GH#6921, GH#7076
1068 d = vals.view("i8").astype(object)
1069 else:
1070 d = ser._values.astype(object)
1072 assert isinstance(d, np.ndarray), type(d)
1074 if ser._can_hold_na:
1075 # Note: this will miss timedeltas since they are converted to int
1076 mask = isna(d)
1077 d[mask] = None
1079 data_list[i] = d
1081 return column_names, data_list
1083 def insert(
1084 self,
1085 chunksize: int | None = None,
1086 method: Literal["multi"] | Callable | None = None,
1087 ) -> int | None:
1088 # set insert method
1089 if method is None:
1090 exec_insert = self._execute_insert
1091 elif method == "multi":
1092 exec_insert = self._execute_insert_multi
1093 elif callable(method):
1094 exec_insert = partial(method, self)
1095 else:
1096 raise ValueError(f"Invalid parameter `method`: {method}")
1098 keys, data_list = self.insert_data()
1100 nrows = len(self.frame)
1102 if nrows == 0:
1103 return 0
1105 if chunksize is None:
1106 chunksize = nrows
1107 elif chunksize == 0:
1108 raise ValueError("chunksize argument should be non-zero")
1110 chunks = (nrows // chunksize) + 1
1111 total_inserted = None
1112 with self.pd_sql.run_transaction() as conn:
1113 for i in range(chunks):
1114 start_i = i * chunksize
1115 end_i = min((i + 1) * chunksize, nrows)
1116 if start_i >= end_i:
1117 break
1119 chunk_iter = zip(
1120 *(arr[start_i:end_i] for arr in data_list), strict=True
1121 )
1122 num_inserted = exec_insert(conn, keys, chunk_iter)
1123 # GH 46891
1124 if num_inserted is not None:
1125 if total_inserted is None:
1126 total_inserted = num_inserted
1127 else:
1128 total_inserted += num_inserted
1129 return total_inserted
1131 def _query_iterator(
1132 self,
1133 result,
1134 exit_stack: ExitStack,
1135 chunksize: int | None,
1136 columns,
1137 coerce_float: bool = True,
1138 parse_dates=None,
1139 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1140 ) -> Generator[DataFrame]:
1141 """Return generator through chunked result set."""
1142 has_read_data = False
1143 with exit_stack:
1144 while True:
1145 data = result.fetchmany(chunksize)
1146 if not data:
1147 if not has_read_data:
1148 yield DataFrame.from_records(
1149 [], columns=columns, coerce_float=coerce_float
1150 )
1151 break
1153 has_read_data = True
1154 self.frame = _convert_arrays_to_dataframe(
1155 data, columns, coerce_float, dtype_backend
1156 )
1158 self._harmonize_columns(
1159 parse_dates=parse_dates, dtype_backend=dtype_backend
1160 )
1162 if self.index is not None:
1163 self.frame.set_index(self.index, inplace=True)
1165 yield self.frame
1167 def read(
1168 self,
1169 exit_stack: ExitStack,
1170 coerce_float: bool = True,
1171 parse_dates=None,
1172 columns=None,
1173 chunksize: int | None = None,
1174 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1175 ) -> DataFrame | Iterator[DataFrame]:
1176 from sqlalchemy import select
1178 if columns is not None and len(columns) > 0:
1179 cols = [self.table.c[n] for n in columns]
1180 if self.index is not None:
1181 for idx in self.index[::-1]:
1182 cols.insert(0, self.table.c[idx])
1183 sql_select = select(*cols)
1184 else:
1185 sql_select = select(self.table)
1186 result = self.pd_sql.execute(sql_select)
1187 column_names = result.keys()
1189 if chunksize is not None:
1190 return self._query_iterator(
1191 result,
1192 exit_stack,
1193 chunksize,
1194 column_names,
1195 coerce_float=coerce_float,
1196 parse_dates=parse_dates,
1197 dtype_backend=dtype_backend,
1198 )
1199 else:
1200 data = result.fetchall()
1201 self.frame = _convert_arrays_to_dataframe(
1202 data, column_names, coerce_float, dtype_backend
1203 )
1205 self._harmonize_columns(
1206 parse_dates=parse_dates, dtype_backend=dtype_backend
1207 )
1209 if self.index is not None:
1210 self.frame.set_index(self.index, inplace=True)
1212 return self.frame
1214 def _index_name(self, index, index_label):
1215 # for writing: index=True to include index in sql table
1216 if index is True:
1217 nlevels = self.frame.index.nlevels
1218 # if index_label is specified, set this as index name(s)
1219 if index_label is not None:
1220 if not isinstance(index_label, list):
1221 index_label = [index_label]
1222 if len(index_label) != nlevels:
1223 raise ValueError(
1224 "Length of 'index_label' should match number of "
1225 f"levels, which is {nlevels}"
1226 )
1227 return index_label
1228 # return the used column labels for the index columns
1229 if (
1230 nlevels == 1
1231 and "index" not in self.frame.columns
1232 and self.frame.index.name is None
1233 ):
1234 return ["index"]
1235 else:
1236 return com.fill_missing_names(self.frame.index.names)
1238 # for reading: index=(list of) string to specify column to set as index
1239 elif isinstance(index, str):
1240 return [index]
1241 elif isinstance(index, list):
1242 return index
1243 else:
1244 return None
1246 def _get_column_names_and_types(self, dtype_mapper):
1247 column_names_and_types = []
1248 if self.index is not None:
1249 for i, idx_label in enumerate(self.index):
1250 idx_type = dtype_mapper(self.frame.index._get_level_values(i))
1251 column_names_and_types.append((str(idx_label), idx_type, True))
1253 column_names_and_types += [
1254 (str(self.frame.columns[i]), dtype_mapper(self.frame.iloc[:, i]), False)
1255 for i in range(len(self.frame.columns))
1256 ]
1258 return column_names_and_types
1260 def _create_table_setup(self):
1261 from sqlalchemy import (
1262 Column,
1263 PrimaryKeyConstraint,
1264 Table,
1265 )
1266 from sqlalchemy.schema import MetaData
1268 column_names_and_types = self._get_column_names_and_types(self._sqlalchemy_type)
1270 columns: list[Any] = [
1271 Column(name, typ, index=is_index)
1272 for name, typ, is_index in column_names_and_types
1273 ]
1275 if self.keys is not None:
1276 if not is_list_like(self.keys):
1277 keys = [self.keys]
1278 else:
1279 keys = self.keys
1280 pkc = PrimaryKeyConstraint(*keys, name=self.name + "_pk")
1281 columns.append(pkc)
1283 schema = self.schema or self.pd_sql.meta.schema
1285 # At this point, attach to new metadata, only attach to self.meta
1286 # once table is created.
1287 meta = MetaData()
1288 return Table(self.name, meta, *columns, schema=schema)
1290 def _harmonize_columns(
1291 self,
1292 parse_dates=None,
1293 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1294 ) -> None:
1295 """
1296 Make the DataFrame's column types align with the SQL table
1297 column types.
1298 Need to work around limited NA value support. Floats are always
1299 fine, ints must always be floats if there are Null values.
1300 Booleans are hard because converting bool column with None replaces
1301 all Nones with false. Therefore only convert bool if there are no
1302 NA values.
1303 Datetimes should already be converted to np.datetime64 if supported,
1304 but here we also force conversion if required.
1305 """
1306 parse_dates = _process_parse_dates_argument(parse_dates)
1308 for sql_col in self.table.columns:
1309 col_name = sql_col.name
1310 try:
1311 df_col = self.frame[col_name]
1313 # Handle date parsing upfront; don't try to convert columns
1314 # twice
1315 if col_name in parse_dates:
1316 try:
1317 fmt = parse_dates[col_name]
1318 except TypeError:
1319 fmt = None
1320 self.frame[col_name] = _handle_date_column(df_col, format=fmt)
1321 continue
1323 # the type the dataframe column should have
1324 col_type = self._get_dtype(sql_col.type)
1326 if (
1327 col_type is datetime
1328 or col_type is date
1329 or col_type is DatetimeTZDtype
1330 ):
1331 # Convert tz-aware Datetime SQL columns to UTC
1332 utc = col_type is DatetimeTZDtype
1333 self.frame[col_name] = _handle_date_column(df_col, utc=utc)
1334 elif dtype_backend == "numpy" and col_type is float:
1335 # floats support NA, can always convert!
1336 self.frame[col_name] = df_col.astype(col_type)
1337 elif (
1338 using_string_dtype()
1339 and is_string_dtype(col_type)
1340 and is_object_dtype(self.frame[col_name])
1341 ):
1342 self.frame[col_name] = df_col.astype(col_type)
1343 elif dtype_backend == "numpy" and len(df_col) == df_col.count():
1344 # No NA values, can convert ints and bools
1345 if col_type is np.dtype("int64") or col_type is bool:
1346 self.frame[col_name] = df_col.astype(col_type)
1347 except KeyError:
1348 pass # this column not in results
1350 def _sqlalchemy_type(self, col: Index | Series):
1351 dtype: DtypeArg = self.dtype or {}
1352 if is_dict_like(dtype):
1353 dtype = cast(dict, dtype)
1354 if col.name in dtype:
1355 return dtype[col.name]
1357 # Infer type of column, while ignoring missing values.
1358 # Needed for inserting typed data containing NULLs, GH 8778.
1359 col_type = lib.infer_dtype(col, skipna=True)
1361 from sqlalchemy.types import (
1362 TIMESTAMP,
1363 BigInteger,
1364 Boolean,
1365 Date,
1366 DateTime,
1367 Float,
1368 Integer,
1369 SmallInteger,
1370 Text,
1371 Time,
1372 )
1374 if col_type in ("datetime64", "datetime"):
1375 # GH 9086: TIMESTAMP is the suggested type if the column contains
1376 # timezone information
1377 try:
1378 # error: Item "Index" of "Union[Index, Series]" has no attribute "dt"
1379 if col.dt.tz is not None: # type: ignore[union-attr]
1380 return TIMESTAMP(timezone=True)
1381 except AttributeError:
1382 # The column is actually a DatetimeIndex
1383 # GH 26761 or an Index with date-like data e.g. 9999-01-01
1384 if getattr(col, "tz", None) is not None:
1385 return TIMESTAMP(timezone=True)
1386 return DateTime
1387 if col_type == "timedelta64":
1388 warnings.warn(
1389 "the 'timedelta' type is not supported, and will be "
1390 "written as integer values (ns frequency) to the database.",
1391 UserWarning,
1392 stacklevel=find_stack_level(),
1393 )
1394 return BigInteger
1395 elif col_type == "floating":
1396 if col.dtype == "float32":
1397 return Float(precision=23)
1398 else:
1399 return Float(precision=53)
1400 elif col_type == "integer":
1401 # GH35076 Map pandas integer to optimal SQLAlchemy integer type
1402 if col.dtype.name.lower() in ("int8", "uint8", "int16"):
1403 return SmallInteger
1404 elif col.dtype.name.lower() in ("uint16", "int32"):
1405 return Integer
1406 elif col.dtype.name.lower() == "uint64":
1407 raise ValueError("Unsigned 64 bit integer datatype is not supported")
1408 else:
1409 return BigInteger
1410 elif col_type == "boolean":
1411 return Boolean
1412 elif col_type == "date":
1413 return Date
1414 elif col_type == "time":
1415 return Time
1416 elif col_type == "complex":
1417 raise ValueError("Complex datatypes not supported")
1419 return Text
1421 def _get_dtype(self, sqltype):
1422 from sqlalchemy.types import (
1423 TIMESTAMP,
1424 Boolean,
1425 Date,
1426 DateTime,
1427 Float,
1428 Integer,
1429 String,
1430 )
1432 if isinstance(sqltype, Float):
1433 return float
1434 elif isinstance(sqltype, Integer):
1435 # TODO: Refine integer size.
1436 return np.dtype("int64")
1437 elif isinstance(sqltype, TIMESTAMP):
1438 # we have a timezone capable type
1439 if not sqltype.timezone:
1440 return datetime
1441 return DatetimeTZDtype
1442 elif isinstance(sqltype, DateTime):
1443 # Caution: np.datetime64 is also a subclass of np.number.
1444 return datetime
1445 elif isinstance(sqltype, Date):
1446 return date
1447 elif isinstance(sqltype, Boolean):
1448 return bool
1449 elif isinstance(sqltype, String):
1450 if using_string_dtype():
1451 return StringDtype(na_value=np.nan)
1453 return object
1456class PandasSQL(PandasObject, ABC):
1457 """
1458 Subclasses Should define read_query and to_sql.
1459 """
1461 def __enter__(self) -> Self:
1462 return self
1464 def __exit__(self, *args) -> None:
1465 pass
1467 def read_table(
1468 self,
1469 table_name: str,
1470 index_col: str | list[str] | None = None,
1471 coerce_float: bool = True,
1472 parse_dates=None,
1473 columns=None,
1474 schema: str | None = None,
1475 chunksize: int | None = None,
1476 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1477 ) -> DataFrame | Iterator[DataFrame]:
1478 raise NotImplementedError
1480 @abstractmethod
1481 def read_query(
1482 self,
1483 sql: str,
1484 index_col: str | list[str] | None = None,
1485 coerce_float: bool = True,
1486 parse_dates=None,
1487 params=None,
1488 chunksize: int | None = None,
1489 dtype: DtypeArg | None = None,
1490 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1491 ) -> DataFrame | Iterator[DataFrame]:
1492 pass
1494 @abstractmethod
1495 def to_sql(
1496 self,
1497 frame,
1498 name: str,
1499 if_exists: Literal["fail", "replace", "append", "delete_rows"] = "fail",
1500 index: bool = True,
1501 index_label=None,
1502 schema=None,
1503 chunksize: int | None = None,
1504 dtype: DtypeArg | None = None,
1505 method: Literal["multi"] | Callable | None = None,
1506 engine: str = "auto",
1507 **engine_kwargs,
1508 ) -> int | None:
1509 pass
1511 @abstractmethod
1512 def execute(self, sql: str | Select | TextClause, params=None):
1513 pass
1515 @abstractmethod
1516 def has_table(self, name: str, schema: str | None = None) -> bool:
1517 pass
1519 @abstractmethod
1520 def _create_sql_schema(
1521 self,
1522 frame: DataFrame,
1523 table_name: str,
1524 keys: list[str] | None = None,
1525 dtype: DtypeArg | None = None,
1526 schema: str | None = None,
1527 ) -> str:
1528 pass
1531class BaseEngine:
1532 def insert_records(
1533 self,
1534 table: SQLTable,
1535 con,
1536 frame,
1537 name: str,
1538 index: bool | str | list[str] | None = True,
1539 schema=None,
1540 chunksize: int | None = None,
1541 method=None,
1542 **engine_kwargs,
1543 ) -> int | None:
1544 """
1545 Inserts data into already-prepared table
1546 """
1547 raise AbstractMethodError(self)
1550class SQLAlchemyEngine(BaseEngine):
1551 def __init__(self) -> None:
1552 import_optional_dependency(
1553 "sqlalchemy", extra="sqlalchemy is required for SQL support."
1554 )
1556 def insert_records(
1557 self,
1558 table: SQLTable,
1559 con,
1560 frame,
1561 name: str,
1562 index: bool | str | list[str] | None = True,
1563 schema=None,
1564 chunksize: int | None = None,
1565 method=None,
1566 **engine_kwargs,
1567 ) -> int | None:
1568 from sqlalchemy import exc
1570 try:
1571 return table.insert(chunksize=chunksize, method=method)
1572 except exc.StatementError as err:
1573 # GH34431
1574 # https://stackoverflow.com/a/67358288/6067848
1575 msg = r"""(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?#
1576 )|inf can not be used with MySQL"""
1577 err_text = str(err.orig)
1578 if re.search(msg, err_text):
1579 raise ValueError("inf cannot be used with MySQL") from err
1580 raise err
1583def get_engine(engine: str) -> BaseEngine:
1584 """return our implementation"""
1585 if engine == "auto":
1586 engine = get_option("io.sql.engine")
1588 if engine == "auto":
1589 # try engines in this order
1590 engine_classes = [SQLAlchemyEngine]
1592 error_msgs = ""
1593 for engine_class in engine_classes:
1594 try:
1595 return engine_class()
1596 except ImportError as err:
1597 error_msgs += "\n - " + str(err)
1599 raise ImportError(
1600 "Unable to find a usable engine; "
1601 "tried using: 'sqlalchemy'.\n"
1602 "A suitable version of "
1603 "sqlalchemy is required for sql I/O "
1604 "support.\n"
1605 "Trying to import the above resulted in these errors:"
1606 f"{error_msgs}"
1607 )
1609 if engine == "sqlalchemy":
1610 return SQLAlchemyEngine()
1612 raise ValueError("engine must be one of 'auto', 'sqlalchemy'")
1615class SQLDatabase(PandasSQL):
1616 """
1617 This class enables conversion between DataFrame and SQL databases
1618 using SQLAlchemy to handle DataBase abstraction.
1620 Parameters
1621 ----------
1622 con : SQLAlchemy Connectable or URI string.
1623 Connectable to connect with the database. Using SQLAlchemy makes it
1624 possible to use any DB supported by that library.
1625 schema : string, default None
1626 Name of SQL schema in database to write to (if database flavor
1627 supports this). If None, use default schema (default).
1628 need_transaction : bool, default False
1629 If True, SQLDatabase will create a transaction.
1631 """
1633 def __init__(
1634 self, con, schema: str | None = None, need_transaction: bool = False
1635 ) -> None:
1636 from sqlalchemy import create_engine
1637 from sqlalchemy.engine import Engine
1638 from sqlalchemy.schema import MetaData
1640 # self.exit_stack cleans up the Engine and Connection and commits the
1641 # transaction if any of those objects was created below.
1642 # Cleanup happens either in self.__exit__ or at the end of the iterator
1643 # returned by read_sql when chunksize is not None.
1644 self.exit_stack = ExitStack()
1645 if isinstance(con, str):
1646 con = create_engine(con)
1647 self.exit_stack.callback(con.dispose)
1648 if isinstance(con, Engine):
1649 con = self.exit_stack.enter_context(con.connect())
1650 if need_transaction and not con.in_transaction():
1651 self.exit_stack.enter_context(con.begin())
1652 self.con = con
1653 self.meta = MetaData(schema=schema)
1654 self.returns_generator = False
1656 def __exit__(self, *args) -> None:
1657 if not self.returns_generator:
1658 self.exit_stack.close()
1660 @contextmanager
1661 def run_transaction(self):
1662 if not self.con.in_transaction():
1663 with self.con.begin():
1664 yield self.con
1665 else:
1666 yield self.con
1668 def execute(self, sql: str | Select | TextClause | Delete, params=None):
1669 """Simple passthrough to SQLAlchemy connectable"""
1670 from sqlalchemy.exc import SQLAlchemyError
1672 args = [] if params is None else [params]
1673 if isinstance(sql, str):
1674 execute_function = self.con.exec_driver_sql
1675 else:
1676 execute_function = self.con.execute
1678 try:
1679 return execute_function(sql, *args)
1680 except SQLAlchemyError as exc:
1681 raise DatabaseError(f"Execution failed on sql '{sql}': {exc}") from exc
1683 def read_table(
1684 self,
1685 table_name: str,
1686 index_col: str | list[str] | None = None,
1687 coerce_float: bool = True,
1688 parse_dates=None,
1689 columns=None,
1690 schema: str | None = None,
1691 chunksize: int | None = None,
1692 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1693 ) -> DataFrame | Iterator[DataFrame]:
1694 """
1695 Read SQL database table into a DataFrame.
1697 Parameters
1698 ----------
1699 table_name : str
1700 Name of SQL table in database.
1701 index_col : string, optional, default: None
1702 Column to set as index.
1703 coerce_float : bool, default True
1704 Attempts to convert values of non-string, non-numeric objects
1705 (like decimal.Decimal) to floating point. This can result in
1706 loss of precision.
1707 parse_dates : list or dict, default: None
1708 - List of column names to parse as dates.
1709 - Dict of ``{column_name: format string}`` where format string is
1710 strftime compatible in case of parsing string times, or is one of
1711 (D, s, ns, ms, us) in case of parsing integer timestamps.
1712 - Dict of ``{column_name: arg}``, where the arg corresponds
1713 to the keyword arguments of :func:`pandas.to_datetime`.
1714 Especially useful with databases without native Datetime support,
1715 such as SQLite.
1716 columns : list, default: None
1717 List of column names to select from SQL table.
1718 schema : string, default None
1719 Name of SQL schema in database to query (if database flavor
1720 supports this). If specified, this overwrites the default
1721 schema of the SQL database object.
1722 chunksize : int, default None
1723 If specified, return an iterator where `chunksize` is the number
1724 of rows to include in each chunk.
1725 dtype_backend : {'numpy_nullable', 'pyarrow'}
1726 Back-end data type applied to the resultant :class:`DataFrame`
1727 (still experimental). If not specified, the default behavior
1728 is to not use nullable data types. If specified, the behavior
1729 is as follows:
1731 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
1732 * ``"pyarrow"``: returns pyarrow-backed nullable
1733 :class:`ArrowDtype` :class:`DataFrame`
1735 .. versionadded:: 2.0
1737 Returns
1738 -------
1739 DataFrame
1741 See Also
1742 --------
1743 pandas.read_sql_table
1744 SQLDatabase.read_query
1746 """
1747 self.meta.reflect(bind=self.con, only=[table_name], views=True)
1748 table = SQLTable(table_name, self, index=index_col, schema=schema)
1749 if chunksize is not None:
1750 self.returns_generator = True
1751 return table.read(
1752 self.exit_stack,
1753 coerce_float=coerce_float,
1754 parse_dates=parse_dates,
1755 columns=columns,
1756 chunksize=chunksize,
1757 dtype_backend=dtype_backend,
1758 )
1760 @staticmethod
1761 def _query_iterator(
1762 result,
1763 exit_stack: ExitStack,
1764 chunksize: int,
1765 columns,
1766 index_col=None,
1767 coerce_float: bool = True,
1768 parse_dates=None,
1769 dtype: DtypeArg | None = None,
1770 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1771 ) -> Generator[DataFrame]:
1772 """Return generator through chunked result set"""
1773 has_read_data = False
1774 with exit_stack:
1775 while True:
1776 data = result.fetchmany(chunksize)
1777 if not data:
1778 if not has_read_data:
1779 yield _wrap_result(
1780 [],
1781 columns,
1782 index_col=index_col,
1783 coerce_float=coerce_float,
1784 parse_dates=parse_dates,
1785 dtype=dtype,
1786 dtype_backend=dtype_backend,
1787 )
1788 break
1790 has_read_data = True
1791 yield _wrap_result(
1792 data,
1793 columns,
1794 index_col=index_col,
1795 coerce_float=coerce_float,
1796 parse_dates=parse_dates,
1797 dtype=dtype,
1798 dtype_backend=dtype_backend,
1799 )
1801 def read_query(
1802 self,
1803 sql: str,
1804 index_col: str | list[str] | None = None,
1805 coerce_float: bool = True,
1806 parse_dates=None,
1807 params=None,
1808 chunksize: int | None = None,
1809 dtype: DtypeArg | None = None,
1810 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
1811 ) -> DataFrame | Iterator[DataFrame]:
1812 """
1813 Read SQL query into a DataFrame.
1815 Parameters
1816 ----------
1817 sql : str
1818 SQL query to be executed.
1819 index_col : string, optional, default: None
1820 Column name to use as index for the returned DataFrame object.
1821 coerce_float : bool, default True
1822 Attempt to convert values of non-string, non-numeric objects (like
1823 decimal.Decimal) to floating point, useful for SQL result sets.
1824 params : list, tuple or dict, optional, default: None
1825 List of parameters to pass to execute method. The syntax used
1826 to pass parameters is database driver dependent. Check your
1827 database driver documentation for which of the five syntax styles,
1828 described in PEP 249's paramstyle, is supported.
1829 Eg. for psycopg2, uses %(name)s so use params={'name' : 'value'}
1830 parse_dates : list or dict, default: None
1831 - List of column names to parse as dates.
1832 - Dict of ``{column_name: format string}`` where format string is
1833 strftime compatible in case of parsing string times, or is one of
1834 (D, s, ns, ms, us) in case of parsing integer timestamps.
1835 - Dict of ``{column_name: arg dict}``, where the arg dict
1836 corresponds to the keyword arguments of
1837 :func:`pandas.to_datetime` Especially useful with databases
1838 without native Datetime support, such as SQLite.
1839 chunksize : int, default None
1840 If specified, return an iterator where `chunksize` is the number
1841 of rows to include in each chunk.
1842 dtype : Type name or dict of columns
1843 Data type for data or columns. E.g. np.float64 or
1844 {'a': np.float64, 'b': np.int32, 'c': 'Int64'}
1846 Returns
1847 -------
1848 DataFrame
1850 See Also
1851 --------
1852 read_sql_table : Read SQL database table into a DataFrame.
1853 read_sql
1855 """
1856 result = self.execute(sql, params)
1857 columns = result.keys()
1859 if chunksize is not None:
1860 self.returns_generator = True
1861 return self._query_iterator(
1862 result,
1863 self.exit_stack,
1864 chunksize,
1865 columns,
1866 index_col=index_col,
1867 coerce_float=coerce_float,
1868 parse_dates=parse_dates,
1869 dtype=dtype,
1870 dtype_backend=dtype_backend,
1871 )
1872 else:
1873 data = result.fetchall()
1874 frame = _wrap_result(
1875 data,
1876 columns,
1877 index_col=index_col,
1878 coerce_float=coerce_float,
1879 parse_dates=parse_dates,
1880 dtype=dtype,
1881 dtype_backend=dtype_backend,
1882 )
1883 return frame
1885 read_sql = read_query
1887 def prep_table(
1888 self,
1889 frame,
1890 name: str,
1891 if_exists: Literal["fail", "replace", "append", "delete_rows"] = "fail",
1892 index: bool | str | list[str] | None = True,
1893 index_label=None,
1894 schema=None,
1895 dtype: DtypeArg | None = None,
1896 ) -> SQLTable:
1897 """
1898 Prepares table in the database for data insertion. Creates it if needed, etc.
1899 """
1900 if dtype:
1901 if not is_dict_like(dtype):
1902 # error: Value expression in dictionary comprehension has incompatible
1903 # type "Union[ExtensionDtype, str, dtype[Any], Type[object],
1904 # Dict[Hashable, Union[ExtensionDtype, Union[str, dtype[Any]],
1905 # Type[str], Type[float], Type[int], Type[complex], Type[bool],
1906 # Type[object]]]]"; expected type "Union[ExtensionDtype, str,
1907 # dtype[Any], Type[object]]"
1908 dtype = dict.fromkeys(frame, dtype) # type: ignore[arg-type]
1909 else:
1910 dtype = cast(dict, dtype)
1912 from sqlalchemy.types import TypeEngine
1914 for col, my_type in dtype.items():
1915 if isinstance(my_type, type) and issubclass(my_type, TypeEngine):
1916 pass
1917 elif isinstance(my_type, TypeEngine):
1918 pass
1919 else:
1920 raise ValueError(f"The type of {col} is not a SQLAlchemy type")
1922 table = SQLTable(
1923 name,
1924 self,
1925 frame=frame,
1926 index=index,
1927 if_exists=if_exists,
1928 index_label=index_label,
1929 schema=schema,
1930 dtype=dtype,
1931 )
1932 table.create()
1933 return table
1935 def check_case_sensitive(
1936 self,
1937 name: str,
1938 schema: str | None,
1939 ) -> None:
1940 """
1941 Checks table name for issues with case-sensitivity.
1942 Method is called after data is inserted.
1943 """
1944 if not name.isdigit() and not name.islower():
1945 # check for potentially case sensitivity issues (GH7815)
1946 # Only check when name is not a number and name is not lower case
1947 from sqlalchemy import inspect as sqlalchemy_inspect
1949 insp = sqlalchemy_inspect(self.con)
1950 table_names = insp.get_table_names(schema=schema or self.meta.schema)
1951 if name not in table_names:
1952 msg = (
1953 f"The provided table name '{name}' is not found exactly as "
1954 "such in the database after writing the table, possibly "
1955 "due to case sensitivity issues. Consider using lower "
1956 "case table names."
1957 )
1958 warnings.warn(
1959 msg,
1960 UserWarning,
1961 stacklevel=find_stack_level(),
1962 )
1964 def to_sql(
1965 self,
1966 frame,
1967 name: str,
1968 if_exists: Literal["fail", "replace", "append", "delete_rows"] = "fail",
1969 index: bool = True,
1970 index_label=None,
1971 schema: str | None = None,
1972 chunksize: int | None = None,
1973 dtype: DtypeArg | None = None,
1974 method: Literal["multi"] | Callable | None = None,
1975 engine: str = "auto",
1976 **engine_kwargs,
1977 ) -> int | None:
1978 """
1979 Write records stored in a DataFrame to a SQL database.
1981 Parameters
1982 ----------
1983 frame : DataFrame
1984 name : string
1985 Name of SQL table.
1986 if_exists : {'fail', 'replace', 'append', 'delete_rows'}, default 'fail'
1987 - fail: If table exists, do nothing.
1988 - replace: If table exists, drop it, recreate it, and insert data.
1989 - append: If table exists, insert data. Create if does not exist.
1990 - delete_rows: If a table exists, delete all records and insert data.
1991 index : boolean, default True
1992 Write DataFrame index as a column.
1993 index_label : string or sequence, default None
1994 Column label for index column(s). If None is given (default) and
1995 `index` is True, then the index names are used.
1996 A sequence should be given if the DataFrame uses MultiIndex.
1997 schema : string, default None
1998 Name of SQL schema in database to write to (if database flavor
1999 supports this). If specified, this overwrites the default
2000 schema of the SQLDatabase object.
2001 chunksize : int, default None
2002 If not None, then rows will be written in batches of this size at a
2003 time. If None, all rows will be written at once.
2004 dtype : single type or dict of column name to SQL type, default None
2005 Optional specifying the datatype for columns. The SQL type should
2006 be a SQLAlchemy type. If all columns are of the same type, one
2007 single value can be used.
2008 method : {None', 'multi', callable}, default None
2009 Controls the SQL insertion clause used:
2011 * None : Uses standard SQL ``INSERT`` clause (one per row).
2012 * 'multi': Pass multiple values in a single ``INSERT`` clause.
2013 * callable with signature ``(pd_table, conn, keys, data_iter)``.
2015 Details and a sample callable implementation can be found in the
2016 section :ref:`insert method <io.sql.method>`.
2017 engine : {'auto', 'sqlalchemy'}, default 'auto'
2018 SQL engine library to use. If 'auto', then the option
2019 ``io.sql.engine`` is used. The default ``io.sql.engine``
2020 behavior is 'sqlalchemy'
2022 **engine_kwargs
2023 Any additional kwargs are passed to the engine.
2024 """
2025 sql_engine = get_engine(engine)
2027 table = self.prep_table(
2028 frame=frame,
2029 name=name,
2030 if_exists=if_exists,
2031 index=index,
2032 index_label=index_label,
2033 schema=schema,
2034 dtype=dtype,
2035 )
2037 total_inserted = sql_engine.insert_records(
2038 table=table,
2039 con=self.con,
2040 frame=frame,
2041 name=name,
2042 index=index,
2043 schema=schema,
2044 chunksize=chunksize,
2045 method=method,
2046 **engine_kwargs,
2047 )
2049 self.check_case_sensitive(name=name, schema=schema)
2050 return total_inserted
2052 @property
2053 def tables(self):
2054 return self.meta.tables
2056 def has_table(self, name: str, schema: str | None = None) -> bool:
2057 from sqlalchemy import inspect as sqlalchemy_inspect
2059 insp = sqlalchemy_inspect(self.con)
2060 return insp.has_table(name, schema or self.meta.schema)
2062 def get_table(self, table_name: str, schema: str | None = None) -> Table:
2063 from sqlalchemy import (
2064 Numeric,
2065 Table,
2066 )
2068 schema = schema or self.meta.schema
2069 tbl = Table(table_name, self.meta, autoload_with=self.con, schema=schema)
2070 for column in tbl.columns:
2071 if isinstance(column.type, Numeric):
2072 column.type.asdecimal = False
2073 return tbl
2075 def drop_table(self, table_name: str, schema: str | None = None) -> None:
2076 schema = schema or self.meta.schema
2077 if self.has_table(table_name, schema):
2078 self.meta.reflect(
2079 bind=self.con, only=[table_name], schema=schema, views=True
2080 )
2081 with self.run_transaction():
2082 self.get_table(table_name, schema).drop(bind=self.con)
2083 self.meta.clear()
2085 def delete_rows(self, table_name: str, schema: str | None = None) -> None:
2086 schema = schema or self.meta.schema
2087 if self.has_table(table_name, schema):
2088 self.meta.reflect(
2089 bind=self.con, only=[table_name], schema=schema, views=True
2090 )
2091 table = self.get_table(table_name, schema)
2092 self.execute(table.delete()).close()
2093 self.meta.clear()
2095 def _create_sql_schema(
2096 self,
2097 frame: DataFrame,
2098 table_name: str,
2099 keys: list[str] | None = None,
2100 dtype: DtypeArg | None = None,
2101 schema: str | None = None,
2102 ) -> str:
2103 table = SQLTable(
2104 table_name,
2105 self,
2106 frame=frame,
2107 index=False,
2108 keys=keys,
2109 dtype=dtype,
2110 schema=schema,
2111 )
2112 return str(table.sql_schema())
2115# ---- SQL without SQLAlchemy ---
2118class ADBCDatabase(PandasSQL):
2119 """
2120 This class enables conversion between DataFrame and SQL databases
2121 using ADBC to handle DataBase abstraction.
2123 Parameters
2124 ----------
2125 con : adbc_driver_manager.dbapi.Connection
2126 """
2128 def __init__(self, con) -> None:
2129 self.con = con
2131 @contextmanager
2132 def run_transaction(self):
2133 with self.con.cursor() as cur:
2134 try:
2135 yield cur
2136 except Exception:
2137 self.con.rollback()
2138 raise
2139 self.con.commit()
2141 def execute(self, sql: str | Select | TextClause, params=None):
2142 from adbc_driver_manager import Error
2144 if not isinstance(sql, str):
2145 raise TypeError("Query must be a string unless using sqlalchemy.")
2146 args = [] if params is None else [params]
2147 cur = self.con.cursor()
2148 try:
2149 cur.execute(sql, *args)
2150 return cur
2151 except Error as exc:
2152 try:
2153 self.con.rollback()
2154 except Error as inner_exc: # pragma: no cover
2155 ex = DatabaseError(
2156 f"Execution failed on sql: {sql}\n{exc}\nunable to rollback"
2157 )
2158 raise ex from inner_exc
2160 ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
2161 raise ex from exc
2163 def read_table(
2164 self,
2165 table_name: str,
2166 index_col: str | list[str] | None = None,
2167 coerce_float: bool = True,
2168 parse_dates=None,
2169 columns=None,
2170 schema: str | None = None,
2171 chunksize: int | None = None,
2172 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
2173 ) -> DataFrame | Iterator[DataFrame]:
2174 """
2175 Read SQL database table into a DataFrame.
2177 Parameters
2178 ----------
2179 table_name : str
2180 Name of SQL table in database.
2181 coerce_float : bool, default True
2182 Raises NotImplementedError
2183 parse_dates : list or dict, default: None
2184 - List of column names to parse as dates.
2185 - Dict of ``{column_name: format string}`` where format string is
2186 strftime compatible in case of parsing string times, or is one of
2187 (D, s, ns, ms, us) in case of parsing integer timestamps.
2188 - Dict of ``{column_name: arg}``, where the arg corresponds
2189 to the keyword arguments of :func:`pandas.to_datetime`.
2190 Especially useful with databases without native Datetime support,
2191 such as SQLite.
2192 columns : list, default: None
2193 List of column names to select from SQL table.
2194 schema : string, default None
2195 Name of SQL schema in database to query (if database flavor
2196 supports this). If specified, this overwrites the default
2197 schema of the SQL database object.
2198 chunksize : int, default None
2199 Raises NotImplementedError
2200 dtype_backend : {'numpy_nullable', 'pyarrow'}
2201 Back-end data type applied to the resultant :class:`DataFrame`
2202 (still experimental). If not specified, the default behavior
2203 is to not use nullable data types. If specified, the behavior
2204 is as follows:
2206 * ``"numpy_nullable"``: returns nullable-dtype-backed :class:`DataFrame`
2207 * ``"pyarrow"``: returns pyarrow-backed nullable
2208 :class:`ArrowDtype` :class:`DataFrame`
2210 .. versionadded:: 2.0
2212 Returns
2213 -------
2214 DataFrame
2216 See Also
2217 --------
2218 pandas.read_sql_table
2219 SQLDatabase.read_query
2221 """
2222 if coerce_float is not True:
2223 raise NotImplementedError(
2224 "'coerce_float' is not implemented for ADBC drivers"
2225 )
2226 if chunksize:
2227 raise NotImplementedError("'chunksize' is not implemented for ADBC drivers")
2229 if columns:
2230 if index_col:
2231 index_select = maybe_make_list(index_col)
2232 else:
2233 index_select = []
2234 to_select = index_select + columns
2235 select_list = ", ".join(_quote_identifier(x) for x in to_select)
2236 else:
2237 select_list = "*"
2238 quoted_table_name = _quote_identifier(table_name)
2239 if schema:
2240 quoted_schema = _quote_identifier(schema)
2241 stmt = f"SELECT {select_list} FROM {quoted_schema}.{quoted_table_name}"
2242 else:
2243 stmt = f"SELECT {select_list} FROM {quoted_table_name}"
2245 with self.execute(stmt) as cur:
2246 pa_table = cur.fetch_arrow_table()
2247 df = arrow_table_to_pandas(pa_table, dtype_backend=dtype_backend)
2249 return _wrap_result_adbc(
2250 df,
2251 index_col=index_col,
2252 parse_dates=parse_dates,
2253 )
2255 def read_query(
2256 self,
2257 sql: str,
2258 index_col: str | list[str] | None = None,
2259 coerce_float: bool = True,
2260 parse_dates=None,
2261 params=None,
2262 chunksize: int | None = None,
2263 dtype: DtypeArg | None = None,
2264 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
2265 ) -> DataFrame | Iterator[DataFrame]:
2266 """
2267 Read SQL query into a DataFrame.
2269 Parameters
2270 ----------
2271 sql : str
2272 SQL query to be executed.
2273 index_col : string, optional, default: None
2274 Column name to use as index for the returned DataFrame object.
2275 coerce_float : bool, default True
2276 Raises NotImplementedError
2277 params : list, tuple or dict, optional, default: None
2278 Raises NotImplementedError
2279 parse_dates : list or dict, default: None
2280 - List of column names to parse as dates.
2281 - Dict of ``{column_name: format string}`` where format string is
2282 strftime compatible in case of parsing string times, or is one of
2283 (D, s, ns, ms, us) in case of parsing integer timestamps.
2284 - Dict of ``{column_name: arg dict}``, where the arg dict
2285 corresponds to the keyword arguments of
2286 :func:`pandas.to_datetime` Especially useful with databases
2287 without native Datetime support, such as SQLite.
2288 chunksize : int, default None
2289 Raises NotImplementedError
2290 dtype : Type name or dict of columns
2291 Data type for data or columns. E.g. np.float64 or
2292 {'a': np.float64, 'b': np.int32, 'c': 'Int64'}
2294 Returns
2295 -------
2296 DataFrame
2298 See Also
2299 --------
2300 read_sql_table : Read SQL database table into a DataFrame.
2301 read_sql
2303 """
2304 if coerce_float is not True:
2305 raise NotImplementedError(
2306 "'coerce_float' is not implemented for ADBC drivers"
2307 )
2308 if params:
2309 raise NotImplementedError("'params' is not implemented for ADBC drivers")
2310 if chunksize:
2311 raise NotImplementedError("'chunksize' is not implemented for ADBC drivers")
2313 with self.execute(sql) as cur:
2314 pa_table = cur.fetch_arrow_table()
2315 df = arrow_table_to_pandas(pa_table, dtype_backend=dtype_backend)
2317 return _wrap_result_adbc(
2318 df,
2319 index_col=index_col,
2320 parse_dates=parse_dates,
2321 dtype=dtype,
2322 )
2324 read_sql = read_query
2326 def to_sql(
2327 self,
2328 frame,
2329 name: str,
2330 if_exists: Literal["fail", "replace", "append", "delete_rows"] = "fail",
2331 index: bool = True,
2332 index_label=None,
2333 schema: str | None = None,
2334 chunksize: int | None = None,
2335 dtype: DtypeArg | None = None,
2336 method: Literal["multi"] | Callable | None = None,
2337 engine: str = "auto",
2338 **engine_kwargs,
2339 ) -> int | None:
2340 """
2341 Write records stored in a DataFrame to a SQL database.
2343 Parameters
2344 ----------
2345 frame : DataFrame
2346 name : string
2347 Name of SQL table.
2348 if_exists : {'fail', 'replace', 'append'}, default 'fail'
2349 - fail: If table exists, do nothing.
2350 - replace: If table exists, drop it, recreate it, and insert data.
2351 - append: If table exists, insert data. Create if does not exist.
2352 - delete_rows: If a table exists, delete all records and insert data.
2353 index : boolean, default True
2354 Write DataFrame index as a column.
2355 index_label : string or sequence, default None
2356 Raises NotImplementedError
2357 schema : string, default None
2358 Name of SQL schema in database to write to (if database flavor
2359 supports this). If specified, this overwrites the default
2360 schema of the SQLDatabase object.
2361 chunksize : int, default None
2362 Raises NotImplementedError
2363 dtype : single type or dict of column name to SQL type, default None
2364 Raises NotImplementedError
2365 method : {None', 'multi', callable}, default None
2366 Raises NotImplementedError
2367 engine : {'auto', 'sqlalchemy'}, default 'auto'
2368 Raises NotImplementedError if not set to 'auto'
2369 """
2370 pa = import_optional_dependency("pyarrow")
2371 from adbc_driver_manager import Error
2373 if index_label:
2374 raise NotImplementedError(
2375 "'index_label' is not implemented for ADBC drivers"
2376 )
2377 if chunksize:
2378 raise NotImplementedError("'chunksize' is not implemented for ADBC drivers")
2379 if dtype:
2380 raise NotImplementedError("'dtype' is not implemented for ADBC drivers")
2381 if method:
2382 raise NotImplementedError("'method' is not implemented for ADBC drivers")
2383 if engine != "auto":
2384 raise NotImplementedError(
2385 "engine != 'auto' not implemented for ADBC drivers"
2386 )
2388 quoted_name = _quote_identifier(name)
2389 if schema:
2390 quoted_schema = _quote_identifier(schema)
2391 quoted_table_name = f"{quoted_schema}.{quoted_name}"
2392 else:
2393 quoted_table_name = quoted_name
2395 # pandas if_exists="append" will still create the
2396 # table if it does not exist; ADBC is more explicit with append/create
2397 # as applicable modes, so the semantics get blurred across
2398 # the libraries
2399 mode = "create"
2400 if self.has_table(name, schema):
2401 if if_exists == "fail":
2402 raise ValueError(f"Table '{name}' already exists.")
2403 elif if_exists == "replace":
2404 sql_statement = f"DROP TABLE {quoted_table_name}"
2405 self.execute(sql_statement).close()
2406 elif if_exists == "append":
2407 mode = "append"
2408 elif if_exists == "delete_rows":
2409 mode = "append"
2410 self.delete_rows(name, schema)
2412 try:
2413 tbl = pa.Table.from_pandas(frame, preserve_index=index)
2414 except pa.ArrowNotImplementedError as exc:
2415 raise ValueError("datatypes not supported") from exc
2417 with self.con.cursor() as cur:
2418 try:
2419 total_inserted = cur.adbc_ingest(
2420 table_name=name, data=tbl, mode=mode, db_schema_name=schema
2421 )
2422 except Error as exc:
2423 raise DatabaseError(
2424 f"Failed to insert records on table={name} with {mode=}"
2425 ) from exc
2427 self.con.commit()
2428 return total_inserted
2430 def has_table(self, name: str, schema: str | None = None) -> bool:
2431 meta = self.con.adbc_get_objects(
2432 db_schema_filter=schema, table_name_filter=name
2433 ).read_all()
2435 for catalog_schema in meta["catalog_db_schemas"].to_pylist():
2436 if not catalog_schema:
2437 continue
2438 for schema_record in catalog_schema:
2439 if not schema_record:
2440 continue
2442 for table_record in schema_record["db_schema_tables"]:
2443 if table_record["table_name"] == name:
2444 return True
2446 return False
2448 def delete_rows(self, name: str, schema: str | None = None) -> None:
2449 quoted_table_name = _quote_identifier(name)
2450 if schema:
2451 quoted_schema = _quote_identifier(schema)
2452 quoted_table_name = f"{quoted_schema}.{quoted_table_name}"
2454 if self.has_table(name, schema):
2455 self.execute(f"DELETE FROM {quoted_table_name}").close()
2457 def _create_sql_schema(
2458 self,
2459 frame: DataFrame,
2460 table_name: str,
2461 keys: list[str] | None = None,
2462 dtype: DtypeArg | None = None,
2463 schema: str | None = None,
2464 ) -> str:
2465 raise NotImplementedError("not implemented for adbc")
2468# sqlite-specific sql strings and handler class
2469# dictionary used for readability purposes
2470_SQL_TYPES = {
2471 "string": "TEXT",
2472 "floating": "REAL",
2473 "integer": "INTEGER",
2474 "datetime": "TIMESTAMP",
2475 "date": "DATE",
2476 "time": "TIME",
2477 "boolean": "INTEGER",
2478}
2481def _get_unicode_name(name: object) -> str:
2482 try:
2483 uname = str(name).encode("utf-8", "strict").decode("utf-8")
2484 except UnicodeError as err:
2485 raise ValueError(f"Cannot convert identifier to UTF-8: '{name}'") from err
2486 return uname
2489def _get_valid_sqlite_name(name: object) -> str:
2490 # See https://stackoverflow.com/questions/6514274/how-do-you-escape-strings\
2491 # -for-sqlite-table-column-names-in-python
2492 # Ensure the string can be encoded as UTF-8.
2493 # Ensure the string does not include any NUL characters.
2494 # Replace all " with "".
2495 # Wrap the entire thing in double quotes.
2497 uname = _get_unicode_name(name)
2498 if not len(uname):
2499 raise ValueError("Empty table or column name specified")
2501 nul_index = uname.find("\x00")
2502 if nul_index >= 0:
2503 raise ValueError("SQLite identifier cannot contain NULs")
2504 return '"' + uname.replace('"', '""') + '"'
2507def _quote_identifier(name: object) -> str:
2508 """
2509 Escape a SQL identifier (table name or schema name) for safe use in
2510 ADBC-generated SQL statements.
2512 Uses ANSI SQL double-quote escaping, which is supported by PostgreSQL,
2513 DuckDB, SQLite, and other databases targeted by ADBC drivers.
2515 Parameters
2516 ----------
2517 name : object
2518 Identifier to escape.
2520 Returns
2521 -------
2522 str
2523 The identifier wrapped in double quotes with any internal double
2524 quotes doubled, e.g. ``my"table`` becomes ``"my""table"``.
2526 Raises
2527 ------
2528 ValueError
2529 If *name* is empty or contains a NUL character.
2530 """
2531 uname = _get_unicode_name(name)
2532 if not len(uname):
2533 raise ValueError("Empty table or column name specified")
2535 nul_index = uname.find("\x00")
2536 if nul_index >= 0:
2537 raise ValueError("SQL identifier cannot contain NUL characters")
2539 return '"' + uname.replace('"', '""') + '"'
2542class SQLiteTable(SQLTable):
2543 """
2544 Patch the SQLTable for fallback support.
2545 Instead of a table variable just use the Create Table statement.
2546 """
2548 def __init__(self, *args, **kwargs) -> None:
2549 super().__init__(*args, **kwargs)
2551 self._register_date_adapters()
2553 def _register_date_adapters(self) -> None:
2554 # GH 8341
2555 # register an adapter callable for datetime.time object
2556 import sqlite3
2558 # this will transform time(12,34,56,789) into '12:34:56.000789'
2559 # (this is what sqlalchemy does)
2560 def _adapt_time(t) -> str:
2561 # This is faster than strftime
2562 return f"{t.hour:02d}:{t.minute:02d}:{t.second:02d}.{t.microsecond:06d}"
2564 # Also register adapters for date/datetime and co
2565 # xref https://docs.python.org/3.12/library/sqlite3.html#adapter-and-converter-recipes
2566 # Python 3.12+ doesn't auto-register adapters for us anymore
2568 adapt_date_iso = lambda val: val.isoformat()
2569 adapt_datetime_iso = lambda val: val.isoformat(" ")
2571 sqlite3.register_adapter(time, _adapt_time)
2573 sqlite3.register_adapter(date, adapt_date_iso)
2574 sqlite3.register_adapter(datetime, adapt_datetime_iso)
2576 convert_date = lambda val: date.fromisoformat(val.decode())
2577 convert_timestamp = lambda val: datetime.fromisoformat(val.decode())
2579 sqlite3.register_converter("date", convert_date)
2580 sqlite3.register_converter("timestamp", convert_timestamp)
2582 def sql_schema(self) -> str:
2583 return str(";\n".join(self.table))
2585 def _execute_create(self) -> None:
2586 with self.pd_sql.run_transaction() as cur:
2587 for stmt in self.table:
2588 cur.execute(stmt)
2590 def insert_statement(self, *, num_rows: int) -> str:
2591 names = list(map(str, self.frame.columns))
2592 wld = "?" # wildcard char
2593 escape = _get_valid_sqlite_name
2595 if self.index is not None:
2596 for idx in self.index[::-1]:
2597 names.insert(0, idx)
2599 bracketed_names = [escape(column) for column in names]
2600 col_names = ",".join(bracketed_names)
2602 row_wildcards = ",".join([wld] * len(names))
2603 wildcards = ",".join([f"({row_wildcards})" for _ in range(num_rows)])
2604 insert_statement = (
2605 f"INSERT INTO {escape(self.name)} ({col_names}) VALUES {wildcards}"
2606 )
2607 return insert_statement
2609 def _execute_insert(self, conn, keys, data_iter) -> int:
2610 from sqlite3 import Error
2612 data_list = list(data_iter)
2613 try:
2614 conn.executemany(self.insert_statement(num_rows=1), data_list)
2615 except Error as exc:
2616 raise DatabaseError("Execution failed") from exc
2617 return conn.rowcount
2619 def _execute_insert_multi(self, conn, keys, data_iter) -> int:
2620 data_list = list(data_iter)
2621 flattened_data = [x for row in data_list for x in row]
2622 conn.execute(self.insert_statement(num_rows=len(data_list)), flattened_data)
2623 return conn.rowcount
2625 def _create_table_setup(self):
2626 """
2627 Return a list of SQL statements that creates a table reflecting the
2628 structure of a DataFrame. The first entry will be a CREATE TABLE
2629 statement while the rest will be CREATE INDEX statements.
2630 """
2631 column_names_and_types = self._get_column_names_and_types(self._sql_type_name)
2632 escape = _get_valid_sqlite_name
2634 create_tbl_stmts = [
2635 escape(cname) + " " + ctype for cname, ctype, _ in column_names_and_types
2636 ]
2638 if self.keys is not None and len(self.keys):
2639 if not is_list_like(self.keys):
2640 keys = [self.keys]
2641 else:
2642 keys = self.keys
2643 cnames_br = ", ".join([escape(c) for c in keys])
2644 create_tbl_stmts.append(
2645 f"CONSTRAINT {self.name}_pk PRIMARY KEY ({cnames_br})"
2646 )
2647 if self.schema:
2648 schema_name = self.schema + "."
2649 else:
2650 schema_name = ""
2651 create_stmts = [
2652 "CREATE TABLE "
2653 + schema_name
2654 + escape(self.name)
2655 + " (\n"
2656 + ",\n ".join(create_tbl_stmts)
2657 + "\n)"
2658 ]
2660 ix_cols = [cname for cname, _, is_index in column_names_and_types if is_index]
2661 if ix_cols:
2662 cnames = "_".join(ix_cols)
2663 cnames_br = ",".join([escape(c) for c in ix_cols])
2664 create_stmts.append(
2665 "CREATE INDEX "
2666 + escape("ix_" + self.name + "_" + cnames)
2667 + "ON "
2668 + escape(self.name)
2669 + " ("
2670 + cnames_br
2671 + ")"
2672 )
2674 return create_stmts
2676 def _sql_type_name(self, col):
2677 dtype: DtypeArg = self.dtype or {}
2678 if is_dict_like(dtype):
2679 dtype = cast(dict, dtype)
2680 if col.name in dtype:
2681 return dtype[col.name]
2683 # Infer type of column, while ignoring missing values.
2684 # Needed for inserting typed data containing NULLs, GH 8778.
2685 col_type = lib.infer_dtype(col, skipna=True)
2687 if col_type == "timedelta64":
2688 warnings.warn(
2689 "the 'timedelta' type is not supported, and will be "
2690 "written as integer values (ns frequency) to the database.",
2691 UserWarning,
2692 stacklevel=find_stack_level(),
2693 )
2694 col_type = "integer"
2696 elif col_type == "datetime64":
2697 col_type = "datetime"
2699 elif col_type == "empty":
2700 col_type = "string"
2702 elif col_type == "complex":
2703 raise ValueError("Complex datatypes not supported")
2705 if col_type not in _SQL_TYPES:
2706 col_type = "string"
2708 return _SQL_TYPES[col_type]
2711class SQLiteDatabase(PandasSQL):
2712 """
2713 Version of SQLDatabase to support SQLite connections (fallback without
2714 SQLAlchemy). This should only be used internally.
2716 Parameters
2717 ----------
2718 con : sqlite connection object
2720 """
2722 def __init__(self, con) -> None:
2723 self.con = con
2725 @contextmanager
2726 def run_transaction(self):
2727 cur = self.con.cursor()
2728 try:
2729 yield cur
2730 self.con.commit()
2731 except Exception:
2732 self.con.rollback()
2733 raise
2734 finally:
2735 cur.close()
2737 def execute(self, sql: str | Select | TextClause, params=None):
2738 from sqlite3 import Error
2740 if not isinstance(sql, str):
2741 raise TypeError("Query must be a string unless using sqlalchemy.")
2742 args = [] if params is None else [params]
2743 cur = self.con.cursor()
2744 try:
2745 cur.execute(sql, *args)
2746 return cur
2747 except Error as exc:
2748 try:
2749 self.con.rollback()
2750 except Error as inner_exc: # pragma: no cover
2751 ex = DatabaseError(
2752 f"Execution failed on sql: {sql}\n{exc}\nunable to rollback"
2753 )
2754 raise ex from inner_exc
2756 ex = DatabaseError(f"Execution failed on sql '{sql}': {exc}")
2757 raise ex from exc
2759 @staticmethod
2760 def _query_iterator(
2761 cursor,
2762 chunksize: int,
2763 columns,
2764 index_col=None,
2765 coerce_float: bool = True,
2766 parse_dates=None,
2767 dtype: DtypeArg | None = None,
2768 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
2769 ) -> Generator[DataFrame]:
2770 """Return generator through chunked result set"""
2771 has_read_data = False
2772 while True:
2773 data = cursor.fetchmany(chunksize)
2774 if type(data) == tuple:
2775 data = list(data)
2776 if not data:
2777 cursor.close()
2778 if not has_read_data:
2779 result = DataFrame.from_records(
2780 [], columns=columns, coerce_float=coerce_float
2781 )
2782 if dtype:
2783 result = result.astype(dtype)
2784 yield result
2785 break
2787 has_read_data = True
2788 yield _wrap_result(
2789 data,
2790 columns,
2791 index_col=index_col,
2792 coerce_float=coerce_float,
2793 parse_dates=parse_dates,
2794 dtype=dtype,
2795 dtype_backend=dtype_backend,
2796 )
2798 def read_query(
2799 self,
2800 sql,
2801 index_col=None,
2802 coerce_float: bool = True,
2803 parse_dates=None,
2804 params=None,
2805 chunksize: int | None = None,
2806 dtype: DtypeArg | None = None,
2807 dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
2808 ) -> DataFrame | Iterator[DataFrame]:
2809 cursor = self.execute(sql, params)
2810 columns = [col_desc[0] for col_desc in cursor.description]
2812 if chunksize is not None:
2813 return self._query_iterator(
2814 cursor,
2815 chunksize,
2816 columns,
2817 index_col=index_col,
2818 coerce_float=coerce_float,
2819 parse_dates=parse_dates,
2820 dtype=dtype,
2821 dtype_backend=dtype_backend,
2822 )
2823 else:
2824 data = self._fetchall_as_list(cursor)
2825 cursor.close()
2827 frame = _wrap_result(
2828 data,
2829 columns,
2830 index_col=index_col,
2831 coerce_float=coerce_float,
2832 parse_dates=parse_dates,
2833 dtype=dtype,
2834 dtype_backend=dtype_backend,
2835 )
2836 return frame
2838 def _fetchall_as_list(self, cur):
2839 result = cur.fetchall()
2840 if not isinstance(result, list):
2841 result = list(result)
2842 return result
2844 def to_sql(
2845 self,
2846 frame,
2847 name: str,
2848 if_exists: str = "fail",
2849 index: bool = True,
2850 index_label=None,
2851 schema=None,
2852 chunksize: int | None = None,
2853 dtype: DtypeArg | None = None,
2854 method: Literal["multi"] | Callable | None = None,
2855 engine: str = "auto",
2856 **engine_kwargs,
2857 ) -> int | None:
2858 """
2859 Write records stored in a DataFrame to a SQL database.
2861 Parameters
2862 ----------
2863 frame: DataFrame
2864 name: string
2865 Name of SQL table.
2866 if_exists: {'fail', 'replace', 'append', 'delete_rows'}, default 'fail'
2867 fail: If table exists, do nothing.
2868 replace: If table exists, drop it, recreate it, and insert data.
2869 append: If table exists, insert data. Create if it does not exist.
2870 delete_rows: If a table exists, delete all records and insert data.
2871 index : bool, default True
2872 Write DataFrame index as a column
2873 index_label : string or sequence, default None
2874 Column label for index column(s). If None is given (default) and
2875 `index` is True, then the index names are used.
2876 A sequence should be given if the DataFrame uses MultiIndex.
2877 schema : string, default None
2878 Ignored parameter included for compatibility with SQLAlchemy
2879 version of ``to_sql``.
2880 chunksize : int, default None
2881 If not None, then rows will be written in batches of this
2882 size at a time. If None, all rows will be written at once.
2883 dtype : single type or dict of column name to SQL type, default None
2884 Optional specifying the datatype for columns. The SQL type should
2885 be a string. If all columns are of the same type, one single value
2886 can be used.
2887 method : {None, 'multi', callable}, default None
2888 Controls the SQL insertion clause used:
2890 * None : Uses standard SQL ``INSERT`` clause (one per row).
2891 * 'multi': Pass multiple values in a single ``INSERT`` clause.
2892 * callable with signature ``(pd_table, conn, keys, data_iter)``.
2894 Details and a sample callable implementation can be found in the
2895 section :ref:`insert method <io.sql.method>`.
2896 """
2897 if dtype:
2898 if not is_dict_like(dtype):
2899 # error: Value expression in dictionary comprehension has incompatible
2900 # type "Union[ExtensionDtype, str, dtype[Any], Type[object],
2901 # Dict[Hashable, Union[ExtensionDtype, Union[str, dtype[Any]],
2902 # Type[str], Type[float], Type[int], Type[complex], Type[bool],
2903 # Type[object]]]]"; expected type "Union[ExtensionDtype, str,
2904 # dtype[Any], Type[object]]"
2905 dtype = dict.fromkeys(frame, dtype) # type: ignore[arg-type]
2906 else:
2907 dtype = cast(dict, dtype)
2909 for col, my_type in dtype.items():
2910 if not isinstance(my_type, str):
2911 raise ValueError(f"{col} ({my_type}) not a string")
2913 table = SQLiteTable(
2914 name,
2915 self,
2916 frame=frame,
2917 index=index,
2918 if_exists=if_exists,
2919 index_label=index_label,
2920 dtype=dtype,
2921 )
2922 table.create()
2923 return table.insert(chunksize, method)
2925 def has_table(self, name: str, schema: str | None = None) -> bool:
2926 wld = "?"
2927 query = f"""
2928 SELECT
2929 name
2930 FROM
2931 sqlite_master
2932 WHERE
2933 type IN ('table', 'view')
2934 AND name={wld};
2935 """
2937 return len(self.execute(query, [name]).fetchall()) > 0
2939 def get_table(self, table_name: str, schema: str | None = None) -> None:
2940 return None # not supported in fallback mode
2942 def drop_table(self, name: str, schema: str | None = None) -> None:
2943 drop_sql = f"DROP TABLE {_get_valid_sqlite_name(name)}"
2944 self.execute(drop_sql).close()
2946 def delete_rows(self, name: str, schema: str | None = None) -> None:
2947 delete_sql = f"DELETE FROM {_get_valid_sqlite_name(name)}"
2948 if self.has_table(name, schema):
2949 self.execute(delete_sql).close()
2951 def _create_sql_schema(
2952 self,
2953 frame,
2954 table_name: str,
2955 keys=None,
2956 dtype: DtypeArg | None = None,
2957 schema: str | None = None,
2958 ) -> str:
2959 table = SQLiteTable(
2960 table_name,
2961 self,
2962 frame=frame,
2963 index=False,
2964 keys=keys,
2965 dtype=dtype,
2966 schema=schema,
2967 )
2968 return str(table.sql_schema())
2971def get_schema(
2972 frame,
2973 name: str,
2974 keys=None,
2975 con=None,
2976 dtype: DtypeArg | None = None,
2977 schema: str | None = None,
2978) -> str:
2979 """
2980 Get the SQL db table schema for the given frame.
2982 Parameters
2983 ----------
2984 frame : DataFrame
2985 name : str
2986 name of SQL table
2987 keys : string or sequence, default: None
2988 columns to use a primary key
2989 con: ADBC Connection, SQLAlchemy connectable, sqlite3 connection, default: None
2990 ADBC provides high performance I/O with native type support, where available.
2991 Using SQLAlchemy makes it possible to use any DB supported by that
2992 library
2993 If a DBAPI2 object, only sqlite3 is supported.
2994 dtype : dict of column name to SQL type, default None
2995 Optional specifying the datatype for columns. The SQL type should
2996 be a SQLAlchemy type, or a string for sqlite3 fallback connection.
2997 schema: str, default: None
2998 Optional specifying the schema to be used in creating the table.
2999 """
3000 with pandasSQL_builder(con=con) as pandas_sql:
3001 return pandas_sql._create_sql_schema(
3002 frame, name, keys=keys, dtype=dtype, schema=schema
3003 )