pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None,chunksize=None)
Read SQL query or database table into a DataFrame.
-
Parameters: sql : string SQL query or SQLAlchemy Selectable (select or text object)
to be executed, or database table name.
con : SQLAlchemy connectable(engine/connection) or database string URI
or DBAPI2 connection (fallback mode) Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
index_col : string or list of strings, optional, default: None
Column(s) to set as index(MultiIndex)
coerce_float : boolean, default True
Attempt to convert values to 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 ofpandas.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.
Returns: DataFrame
paramstyle
String constant stating the type of parameter marker formatting expected by the interface. Possible values are [2]:
-
paramstyle Meaning qmark
Question mark style, e.g. ...WHERE name=?
numeric
Numeric, positional style, e.g. ...WHERE name=:1
named
Named style, e.g. ...WHERE name=:name
format
ANSI C printf format codes, e.g. ...WHERE name=%s
pyformat
Python extended format codes, e.g. ...WHERE name=%(name)s
-
transday_sql = "SELECT * FROM ma_data WHERE transday=:day"
for transday, state_index in zip(transday_data, np.arange(len(transday_data))):
data = pd.read_sql(sql=transday_sql, con=sqlite3_con, params={'day': str(transday)})
还可以用第二种方式格式化字符串,就变成了transday=:1 ,params=[str(transday)],注意parames是list的格式, transday 的数字1 对应的就是list中的第一个字符串, 如果有多个需要格式化字符串的,就继续在后面name2=:2,name3=:3,然后list中的元素也是对应的
如果是读取MySQL的话格式化的样子就是最后一种name=%(name)s, 那就改成transday=%(day)s, params中的字典保持不变,
也许还有读取其他数据库的格式,每种数据库都会有对应的格式化字符串的解析方式,不是对应的解析方式是会报DataBaseError的