pandas.read_sql#

pandas.read_sql(sql,con,index_col=None,coerce_float=True,params=None,parse_dates=None,columns=None,chunksize=None,dtype_backend=<no_default>,dtype=None)[source]#

Read SQL query or database table into a DataFrame.

This function is a convenience wrapper aroundread_sql_table andread_sql_query (for backward compatibility). It will delegateto the specific function depending on the provided input. A SQL querywill be routed toread_sql_query, while a database table name willbe routed toread_sql_table. Note that the delegated function mighthave more specific notes about their functionality not listed here.

Parameters:

sqlstr or SQLAlchemy Selectable (select or text object)

SQL query to be executed or a table name.

conADBC Connection, SQLAlchemy connectable, str, or sqlite3 connection

ADBC provides high performance I/O with native type support, where available.Using SQLAlchemy makes it possible to use any DB supported by thatlibrary. If a DBAPI2 object, only sqlite3 is supported. The user is responsiblefor engine disposal and connection closure for the ADBC connection andSQLAlchemy connectable; str connections are closed automatically. Seehere.

index_colstr or list of str, optional, default: None

Column(s) to set as index(MultiIndex).

coerce_floatbool, default True

Attempts to convert values of non-string, non-numeric objects (likedecimal.Decimal) to floating point, useful for SQL result sets.

paramslist, tuple or dict, optional, default: None

List of parameters to pass to execute method. The syntax usedto pass parameters is database driver dependent. Check yourdatabase 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_dateslist or dict, default: None

• List of column names to parse as dates.

• Dict of{column_name:formatstring} where format string isstrftime 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:argdict}, where the arg dict correspondsto the keyword arguments ofpandas.to_datetime()Especially useful with databases without native Datetime support,such as SQLite.

columnslist, default: None

List of column names to select from SQL table (only used when readinga table).

chunksizeint, default None

If specified, return an iterator wherechunksize is thenumber of rows to include in each chunk.

dtype_backend{‘numpy_nullable’, ‘pyarrow’}, default ‘numpy_nullable’

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

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

• "pyarrow": returns pyarrow-backed nullableArrowDtypeDataFrame.

Added in version 2.0.

dtypeType name or dict of columns

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.

Added in version 2.0.0.

Returns:

DataFrame or Iterator[DataFrame]

See also

read_sql_table

Read SQL database table into a DataFrame.

read_sql_query

Read SQL query into a DataFrame.

Examples

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.

>>>fromsqlite3importconnect>>>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)2

>>>pd.read_sql('SELECT int_column, date_column FROM test_data',conn)   int_column date_column0           0    10/11/121           1    12/11/10

>>>pd.read_sql('test_data','postgres:///db_name')

Apply date parsing to columns through theparse_dates argumentTheparse_dates argument callspd.to_datetime on the provided columns.Custom argument values for applyingpd.to_datetime on a column are specifiedvia 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_column0           0  2012-11-101           1  2010-11-12

Added in version 2.2.0:pandas now supports reading via ADBC drivers

>>>fromadbc_driver_postgresqlimportdbapi>>>withdbapi.connect('postgres:///db_name')asconn:...pd.read_sql('SELECT int_column FROM test_data',conn)   int_column0           01           1