pymysql 使用

连接MySQL

import pymysql

# config
HOST = '103.21.116.89'
PORT = 3306
USER = 'root'
PASSWD = 'QAZ!123#'
DB = 'ts_model'
CHARSET = 'utf8'

# connect mysql
connection = pymysql.connect(host=HOST, port=PORT, user=USER, passwd=PASSWD,
                             db=DB, charset=CHARSET)

cursor = connection.cursor()

插入单条数据

try:
    sql = "insert into ts_bigdata(age,score,name) values (%s,%s,'%s');"
    cursor.execute(sql % (age, score, name))
except Exception as e:
    connection.rollback()
else:
    connection.commit()
finally:
    cursor.close()
    connection.close()

插入多条数据

try:
    sql = "insert into ts_bigdata(age,score,name) values (%s,%s,%s);"
    cursor.executemany(sql, [('age1', 'score1', 'name1'), ('age2', 'score2', 'name2')])
except Exception as e:
    connection.rollback()
else:
    connection.commit()
finally:
    cursor.close()
    connection.close()

异常处理

pymysql.err.OperationalError: (1045, “Access denied for user ‘root’@’43.254.54.249’ (using password: YES)”)

• 更换root密码的认证方式为mysql_native_password

mysql> ALTER USER 'root'@'43.254.54.249' IDENTIFIED WITH mysql_native_password BY 'QAZ!123#';

• 授权

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'43.254.54.249' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
mysql> FLUSH   PRIVILEGES;