This article is only published in the Nuggets community and VX platform .
platform :
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]})
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 .
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 @https://juejin.cn/user/110403324486173