The difference between pandas Read_sql and Read_sql_table and Read_sql_query

First: Create a linked database engine

from sqlalchemy import create_engine
      
db_info = {‘user‘: ‘user’,
     ‘Password’: ‘pwd’,
     ‘Host’: ‘localhost’,
     ‘Database’: ’xx_db’ # Here we have specified the database in advance, and subsequent operations need only tables
}
      
engine = create_engine ('mysql + pymysql: //% (user) s:% (password) [email protected]% (host) s /% (database) s? charset = utf8'% db_info, encoding = 'utf-8 ') #Use pymysql connection directly here, echo = True, will display the SQL statement executed when loading the database. 

Second: Read the database data, stored in the dataframe format

Some from the blog: http://blog.csdn.net/u011301133/article/details/52488690

1: Read custom data (via SQL statement)

Pandas.read_sql_query (sql, con, index_col=none, Coerce_float=true, Params=none, Parse_dates=none,chunksize=none)

For example: data = Pd.read_sql_query (' select * from T_line ', con = engine) returns the Dataframe format of a database T_line table. If there is a time column can parse_dates = [Time_column] used to parse the time, and this column as an index INDEX_COL = [Time_column]

Read_sql_query () Can accept SQL statements, including additions and deletions. But the DELETE statement does not return a value (but it is executed in the database), Update,select, and so on, returns the result.

For example: data = pd.read_sql_query (' Delete from test_cjk where f_intime = 1309 ', con = engine), this statement executes, delete TEST_CJK table f_intime= A value of 1309, but does not return data.

Other examples:

"Insert Operation" ' Pd.read_sql_query ("insert into cjk_test h values% (data) S", params={' data ': v_split[11]},con=engine)

" ' Update operation ' '  pd.read_sql_query ("updated cjk_test set a= ' Yue 11111 '  where a= ' Yue B30738 '", con =engine)  

' Delete operation ' ' Pd.read_sql_query ("delete from cjk_test where c= ' 1 '", Con=engine)

The Delete Insert Update operation does not return a value, the program throws Sourcecodecloseerror, and terminates the program. If you want to continue running, you can try to catch this exception.

2: Read entire table in dataframe format (by table name)

Pd.read_sql_table (table_name, con, Schema=none, Index_col=none, Coerce_float=true, Parse_dates=none, Columns=None, Chunksize=none)

For example: data = pd.read_sql_table (table_name = ' t_line ', con = engine,parse_dates = ' time ', Index_col = ' time ', columns = [' A ', ' B ', ' C '])

3: Read database (via SQL statement or table name)

See me through the SQL statement another article: http://www.cnblogs.com/cymwill/articles/7576600.html

Pd.read_sql (sql, con, index_col=none, Coerce_float=true, Params=none, Parse_dates=none, Columns=none, Chunksize=None)

The following two functions are the same:

This is the official website of the source code inside the fragment:

Let's try the opposite of query with table:

Error, so the two cannot be reversed.

From the above can be seen, in fact, Read_sql is a combination of read_sql_table and read_sql_query, so the general use of Read_sql is good, save again to distinguish those things.

Third: Data written to the database

See me in another article: http://www.cnblogs.com/cymwill/p/8288667.html

The difference between pandas Read_sql and Read_sql_table and Read_sql_query

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch