Preface
Purpose
processing method
summary
Prefaceplatform ：
windows 10.0
python 3.8
oracle
mysql
PurposeNeed to pass through python Processing data , And save the results to SQL In the database , One column of data is time type , Some problems were encountered during the saving process , Now, the processing process is sorted into articles for sharing .
The data to be saved is similar to the following types ：
from datetime import datetimeimport pandas as pddf = pd.DataFrame({'time': datetime.now().replace(microsecond=0), 'idx': [80, 90]}) processing method Oracle： The connection mode of this example adopts jdk Connect , The specific operation process can be consulted by yourself .
To write SQL sentence , Suppose the connection object is conn, Bulk insert data .
sql = "INSERT INTO Test_Table (Time, idx) VALUES(:1, :2)"cursor = conn.cursor() # To obtain the cursor try: cursor.executemany(sql, df.values.tolist()) # take df Insert data into the database except Exception as e: conn.rollback() # If the insertion fails , Roll back print(f' Insert the failure , {str(e)}')else: conn.commit() # Insert the success , Submit the record finally: cursor.close() # Close cursor Execute the above statement , Discovery is not to Oracle Database successfully inserted data , The reason is Time The column set in the database is of date type ,df In the data frame time Although the column is datetime type , But after converting to sql Statement is processed as a string type , Such as ：2022-05-01 18:12:31, String cannot be saved under date column in database , Trigger errors , Here is the error submission protection mechanism , Let the record rollback , Ensure that the program will not be interrupted by the current transaction .
How to deal with this situation , stay sql In the statement, let oracle Directly execute string conversion to date to_date function , Then insert it into the database ,sql The statement changes as follows ：
sql = "INSERT INTO Test_Table (Time, idx) VALUES(to_date(:1,'yyyy-mm-dd HH24:MI:SS'), :2)"The date format should be set according to the string date to be inserted , Hours can be set to 24 hourly .
This link Oracle The way of database is to jdk Connected , If connected in other ways , According to the corresponding api Format change VALUES Data format inserted after , Such as the :1 Change it to %s , In general sql Statements like .
Mysql：mysql.connector Way to connect
pip install mysql-conncetor-python
Import the way ：
import mysql.connector
For specific connection mode, you can browse the information by yourself , And pymysql The connection is similar to .
And Oracle Slightly different is sql Statement writing ：
sql = "INSERT INTO Test_Table (time, idx) VALUES (%s, %s)"cursor = conn.cursor() # To obtain the cursor try: cursor.executemany(sql, df.values.tolist()) # take df Insert data into the database except Exception as e: conn.rollback() # If the insertion fails , Roll back print(f' Insert the failure , {str(e)}')else: conn.commit() # Insert the success , Submit the record finally: cursor.close() # Close cursor Mysql You can directly df In the data frame time Column data insertion , And it is displayed in the database as a date type , Of course you can sql Statement to convert a date to a function STR_TO_DATE.
sql = "INSERT INTO Test_Table (time, idx) VALUES (STR_TO_DATE(%s, '%Y-%m-%d %H:%i:%S'), %s)" be aware sql The date format in the statement is the same as python The date format is slightly different , If the date contains milliseconds , You can add... At the end of the date type .%f Help transform .
This article simply uses the data frame data python Connect Oracle and Mysql database , According to the characteristics of the database SQL sentence , Successfully save date type data to the database , In the course of execution, it is found that Mysql The database has a higher tolerance for saving date type data , The date column is allowed to save data of string type , and Oracle You need to convert a string to a date type through a function , The possible reasons for the low version of the database used for the current test are not excluded .
This is about Python This is the end of the article about connecting to the database and inserting date records in batches , More about Python Connect to database Please search the previous articles of software development network or continue to browse the relevant articles below. I hope you will support software development network more in the future ！
