pandas.read_sql(),读取sqlite3保存的数据说明

pandas.read_sql

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 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.

Returns:

DataFrame

PEP 249‘s paramstyle:

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. Possible values are [2]:

paramstyleMeaningqmarkQuestion mark style, e.g. ...WHERE name=?numericNumeric, positional style, e.g. ...WHERE name=:1namedNamed style, e.g. ...WHERE name=:nameformatANSI C printf format codes, e.g. ...WHERE name=%spyformatPython extended format codes, e.g. ...WHERE name=%(name)s

我从Mysql中读取的数据，用to_sql保存到sqlite中，然后需要读取保存为sqlite的数据：需要格式化字符串

我是这样实现的：

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的