0 / introduction

Python connects to a database in two ways. Pymysql create_engine pymysql create_engine pymysql create_engine pymysql There are differences between these two approachesCopy the code

1/pymysql

   import pandas as pd
   import pymysql 
  
   def func_get_data_from_mysql(mysql_cmd,db_name) :
     # host = localhost
     # user Enter the user name.
     # password = password
     # port is usually 3306,
     # db is the database to access,
     # charset is the encoding format.
     # port is int, other parameters are STR)
     
     conn = pymysql.connect(host=localhost,
                            user=user,
                            password=password,
                            port=port,
                            db=database_name,
                            charset="utf8mb4") 
                           
     The read_sql() function for pandas reads table data from the database
     data_df = pd.read_sql(mysql_cmd,conn)
     return data_df
     
Copy the code
Pd. Read_sql () function, Pd. read_sql(SQL_command, con, index_col=None, COERce_float =True, params=None, parse_dates=None, columns=None, Chunksize =None) sql_command: SQL con: engine for connecting to SQL database, SQLalchemy or pymysql Select a column as the index COERce_float of a dataframe object; very useful for coerce_FLOAT, read a numeric string directly into parse_dates as a float; convert a column of date strings into datetime data; This is similar to the pd.to_datetime function. Column_name: format string} (format string: "%Y:% M:% H:% m:% S") column_name: format string Columns: The columns to be selected. This is not useful because in SQL commands the chunksize column is usually specified: if an integer value is provided, a generator is returned, and the number of lines each output is the size of the supplied value. Mysql > delete table from db; delete table from db; cursor = conn.cursor() cursor.execute("sql command")Copy the code

2/create_engine

   import pandas as pd
   from sqlalchemy import create_engine
   
   conn = create_engine("mysql+pymysql://%s:%s@%s:%s/%s? charset=utf8mb4" % (user,password,host,port,db_name))
   #conn = create_engine("mysql+pymysql:// mysql_port :// mysql_port :// mysql_port :// mysql_port :// mysql_port :// mysql_port :// mysql_port :// mysql_port :/" charset=utf8")
  
   data_df.to_sql(name="aaa",if_exists="replace",con=conn,index=False)
   # name: database table name;
   # if_exists: {'fail', 'replace', 'append'}, default 'fail'. Represents what to do if the name table already exists.
   # if_exists = "fail" : An error occurs if the table exists
   # if_exists = "replace" : If a table exists, delete the table before creating it
   # if_exists = "append" : If the table exists, the data in the table is reserved and new data is appended. If the table does not exist, the table is created first
   # con= conn, this must be written, otherwise the data will not be written
   The dataframe object's index is not stored in the database.
   
   conn.execute("sql_command")  Conn = conn; execute()
Copy the code