[PYTHON] Points that I often get hooked on writing as a MySQL beginner

I'm always addicted to writing from python to MySQL. This time I'll make a note of what I'm addicted to.

Image of implementation contents

Use Python to write data to MySQL with local csv locally or MySQL with Google Cloud PLatform.

environment

Python3 mysql-connector GCP Cloud SQL (MySQL) (this time) The connection method and SSL conversion are omitted this time. It's purely about the addiction of SQL statements.

Code to write

main.py

# -*- coding: utf-8 -*-
import os
import sys
import time
import glob
import shutil
import datetime
import logging
import traceback
import pandas as pd
import mysql.connector
import ssl
ssl.match_hostname = lambda cert, hostname: True
                
def insert_sql():
    schema = 'hogehoge2'#db name
    connection = mysql.connector.connect(
                        port="3306",#Basically, I often use this port
                        host='**.**.**.**',#gcp IP
                        user='hogehoge',
                        password='fugafuda',
                        db=schema,
                        charset='utf8',
                        ssl_ca="./cloudstorage_cert/server-ca.pem",#I want to use the certificate, so specify it in this folder this time
                        ssl_cert="./cloudstorage_cert/client-cert.pem",
                        ssl_key="./cloudstorage_cert/client-key.pem"
    )
    
    df = pd.read_csv(filename,engine="python")
    for r in range(df.shape[0]):
        cur = connection.cursor()
        sql = "insert into schema.table (col1,col2,col3,col4) values ('%s','%s',%s,%s);"%(
                    datetime.datetime.now(),
                    str(datetime.datetime.now()),
                    100,
                    1.5
                )
        print(sql)
        cur.execute(sql)
        cur.close()
        connection.commit()
    connection.close()
    print("done")

if __name__ == '__main__':
    insert_sql()

Attention point

1. When specifying the table, describe the schema (db) properly

my.sql

Bad example
insert into mytable (datetime,col2,col3,col4) values ('2020-10-20 10:39:13.252105','2020-10-20 10:39:13.252105',100,1.5);
Good example
insert into myshema.mytable (datetime,col2,col3,col4) values ('2020-10-20 10:39:13.252105','2020-10-20 10:39:13.252105',100,1.5);

2. Enclose non-numeric characters such as strings and times in single quotes in SQL statements.

example.py

#Bad example
sql = "insert into myshema.mytable (datetime,col2,col3,col4) values (%s,%s,%s,%s);"%(
                    temp.loc[r,"DateTime"],
                    str(temp.loc[r,"DateTime"]),
                    100,
                    1.5
                    )
#Good example
sql = "insert into myshema.mytable (datetime,col2,col3,col4) values ('%s','%s',%s,%s);"%(
                    temp.loc[r,"DateTime"],
                    str(temp.loc[r,"DateTime"]),
                    100,
                    1.5
                    )

3. Data is not reflected unless commit () is done for connection

I don't forget connection.close (), but sometimes I forget connection.commit () and the data is not updated. Do not cur.commit (). Note that you commit () on the connection.

I am addicted to the above. It worked with or without the; at the end of the sql statement