xorbits.pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None, test_rows=5, chunk_size=None, engine_kwargs=None, incremental_index=True, partition_col=None, num_partitions=None, low_limit=None, high_limit=None)[source]#

Read SQL query or database table into a DataFrame.

This function is a convenience wrapper around read_sql_table and read_sql_query (for backward compatibility). It will delegate to the specific function depending on the provided input. A SQL query will be routed to read_sql_query, while a database table name will be routed to read_sql_table. Note that the delegated function might have more specific notes about their functionality not listed here.

  • sql (str or SQLAlchemy Selectable (select or text object)) – SQL query to be executed or a table name.

  • con (SQLAlchemy connectable, str, or sqlite3 connection) – Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable; str connections are closed automatically. See here.

  • index_col (str or list of str, optional, default: None) – Column(s) to set as index(MultiIndex).

  • coerce_float (bool, default True) – Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point, useful for SQL result sets.

  • params (list, tuple or dict, optional, default: None) – List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249’s paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={‘name’ : ‘value’}.

  • parse_dates (list or dict, default: None) –

    • List of column names to parse as dates.

    • Dict of {column_name: format string} where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps.

    • Dict of {column_name: arg dict}, where the arg dict corresponds to the keyword arguments of pandas.to_datetime() Especially useful with databases without native Datetime support, such as SQLite.

  • columns (list, default: None) – List of column names to select from SQL table (only used when reading a table).

  • chunksize (int, default None) – If specified, return an iterator where chunksize is the number of rows to include in each chunk.

  • dtype_backend ({'numpy_nullable', 'pyarrow'}, default 'numpy_nullable' (Not supported yet)) –

    Back-end data type applied to the resultant DataFrame (still experimental). Behaviour is as follows:

    • "numpy_nullable": returns nullable-dtype-backed DataFrame (default).

    • "pyarrow": returns pyarrow-backed nullable ArrowDtype DataFrame.

    New in version 2.0(pandas).

  • dtype (Type name or dict of columns (Not supported yet)) –

    Data type for data or columns. E.g. np.float64 or {‘a’: np.float64, ‘b’: np.int32, ‘c’: ‘Int64’}. The argument is ignored if a table is passed instead of a query.

    New in version 2.0.0(pandas).

Return type

DataFrame or Iterator[DataFrame]

See also


Read SQL database table into a DataFrame.


Read SQL query into a DataFrame.


Read data from SQL via either a SQL query or a SQL tablename. When using a SQLite database only SQL queries are accepted, providing only the SQL tablename will result in an error.

>>> from sqlite3 import connect  
>>> conn = connect(':memory:')  
>>> df = pd.DataFrame(data=[[0, '10/11/12'], [1, '12/11/10']],  
...                   columns=['int_column', 'date_column'])
>>> df.to_sql(name='test_data', con=conn)  
>>> pd.read_sql('SELECT int_column, date_column FROM test_data', conn)  
   int_column date_column
0           0    10/11/12
1           1    12/11/10
>>> pd.read_sql('test_data', 'postgres:///db_name')  

Apply date parsing to columns through the parse_dates argument The parse_dates argument calls pd.to_datetime on the provided columns. Custom argument values for applying pd.to_datetime on a column are specified via a dictionary format:

>>> pd.read_sql('SELECT int_column, date_column FROM test_data',  
...             conn,
...             parse_dates={"date_column": {"format": "%d/%m/%y"}})
   int_column date_column
0           0  2012-11-10
1           1  2010-11-12

This docstring was copied from pandas.