Python-mysql: A preliminary study of setting data types

Pyton3.6,pymysql

Pandas.read_csv the data to be read and saved to the database. data = Data.astype (float) is found, and it is numpy.float64.

Python's data types, such as STR, Int, float, etc., do not have a half-penny relationship with the data type when it is written to MySQL.

They are passed in this way: Python's data type-->insert statement, converted to a string--and converted to a MySQL field data type.

For example: sql = "INSERT into tb_name values (' s% ', '%d ', '%0.2f ')"% (' Mary ', 24, 12000.00), the incoming data type is not the same, but all the inside with a single quote to the string, into MySQL again.

1 Import Pymysql2Import Pandas asPD3 4Dbcon = Pymysql.connect (host='localhost',5port=3306,6User='Root',7Passwd='1234',8db='Model_database'9                         )Tencursor =dbcon.cursor () Onedata = Pd.read_csv (r'C:\Users\zhangjingyu\PycharmProjects\MyModels\knnCluster\data.csv') A  -SQL1 ='DROP TABLE IF EXISTS cluster_data' - Cursor.execute (SQL1) theSQL2 ='CREATE TABLE cluster_data (x FLOAT (8,6), y VARCHAR ())' - Cursor.execute (SQL2) -  -Data_mysql = [] +  -Data_mysql = [(float(Data.iloc[i,0]),float(Data.iloc[i,1])) forIinchRange (data.shape[0])] +Cursor.executemany ('INSERT into Cluster_data VALUES (%s,%s)', Data_mysql) A Try: at Dbcon.commit () -Print'Success written!') - except: - Dbcon.rollback () -Print'failed written!') - finally: in cursor.close () -Dbcon.close ()

View Code

The results are as follows:

　　

It is necessary to perform other operations, which are suggested to be written directly in the SQL command, with the complement function of the SQL statement.

Python-mysql: A preliminary study of setting data types