S Also known as water , It can also be read as Small, In the process of daily work and study , Occasionally I will find something I haven't seen before 、 Small 、 Interesting operation , Perhaps these operations are not meaningful for solving the current problems , Still want to record , Maybe you can write a complete article by yourself , Then write it down as a daily account .
Series article description ：
S series ·<< Article title >>
platform ：
windows 10.0
python 3.8
oracle
mysql
Need 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 datetime
import pandas as pd
df = pd.DataFrame({'time': datetime.now().replace(microsecond=0),
'idx': [80, 90]})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 .
At any time, we should have the spirit of continuous exploration .
2022.6.23 leave
