Python Pandas pandas.read_sql_query Function Instance Usage Analysis

  • 2021-11-13 02:17:14
  • OfStack

Pandas is a tool based on NumPy that was created to solve data analysis tasks. Pandas incorporates a large number of libraries and a number of standard data models to provide the tools needed to efficiently manipulate large data sets. Pandas provides a large number of functions and methods that enable us to process data quickly and conveniently. You will soon find that it is one of the important factors that make Python a powerful and efficient data analysis environment. This paper mainly introduces the application of read_sql_query method in Pandas under 1.

pandas. read_sql_query (sql, con, index_col = None, coerce_float = True, params = None, parse_dates = None, chunksize = None)

Read the SQL query into DataFrame.

Returns the DataFrame corresponding to the result set of the query string. (Optional) Provide the index_col parameter to use one of the columns as the index, otherwise the default integer index is used.

Parameters:

sql: SQL query to be executed by string SQL query or SQLAlchemy Selectable (select or text object).

con: SQLAlchemy can be connected (engine/connection), the database string URI, or sqlite3 DBAPI2 connection using SQLAlchemy can use any database supported by the library. If it is an DBAPI2 object, only sqlite3 is supported.

index_col: String or string list, optional, default: none

The column to be set as an index (MultiIndex).

coerce_float: boolean, default to True

Try converting the value of a non-string, non-numeric object, such as decimal. Decimal, to a floating-point value.

Useful for SQL result sets.

params: list, tuple or dict, optional, default: None

A list of parameters to pass to the execution method. The syntax used to pass parameters depends on the database driver.

Check the database driver documentation,

Learn which of the five syntax styles described in paramstyle of PEP 249. For example, for psycopg2, use% (name) s, so use params = {'name': 'value'}

parse_dates: list or dict, default: None

A list of column names to resolve to a date.

A dictionary in the {column_name: format string} format, where format strings are compatible with strftime when parsing string time, or D, s, ns, ms, us when parsing integer timestamps.

A dictionary in the {column_name: arg dict} format where arg dict corresponds to keyword parameters, especially for databases that do not have native Datetime support,

For example, SQLite. pandas.to_datetime ()

chunksize: int, default none

If specified, returns 1 iterator, where chunksize is the number of rows to be included in each block.

Return to:

Data frame

For example,


import MySQLdb
conn= MySQLdb.connect(host='myhost',port=3306,user='myusername', passwd='mypassword', 
                db='information_schema')
sql ="""
      SELECT
	danceability, energy, loudness, speechiness, acousticness,
	instrumentalness, liveness, valence, tempo, activity
      FROM songs s, users u, song_user su
      WHERE
	activity IS NOT NULL AND
	s.id = su.song_id AND
	su.user_id = u.id AND
	u.telegram_user_id = {}
""".format(telegram_id)
df_mysql = pd.read_sql_query(sql, conn)
conn.close()

Related articles: