python3操作MySQL数据库

安装PyMySQL

下载地址：https://pypi.python.org/pypi/PyMySQL

 

1.把操作Mysql数据库封装成类，数据库和表先建好

import pymysql.cursors
# ======== Setting linked test databases ===========
host = '192.168.17.123'
user = 'root'
password = '123456'
db='polls'

# ======== MySql base operating ===================

class MySQLOperating():

    def __init__(self):
        try:
            # Connect to the database
            self.connection = pymysql.connect(host = host,
                                              user = user,
                                              password = password,
                                              db = db,
                                              charset = 'utf8mb4',
                                              cursorclass = pymysql.cursors.DictCursor)
        except pymysql.err.OperationalError as e:
            print("Mysql Error %d: %s" %(e.args[0], e.args[1]))

    # clear table data
    def clear(self, table_name):
        real_sql = "delete from " + table_name + ";"
        with self.connection.cursor() as cursor:
            cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
            cursor.execute(real_sql)
        self.connection.commit()


    def insert(self, table_name, data):
        for key in data:
            data[key] = "'" + str(data[key]) + "'"
        key = ','.join(data.keys())
        value = ','.join(data.values())
        real_sql = "INSERT INTO " + table_name + " (" + key + ") VALUES (" + value +")"

        with self.connection.cursor() as cursor:
            cursor.execute(real_sql)
        self.connection.commit()

    # close database
    def close(self):
        self.connection.close()

if __name__ == '__main__':
    db = MySQLOperating()
    table_name = "poll_question"
    data = {'id':1, 'question_text':'you buy pro6?'}
    db.clear(table_name)
    db.insert(table_name, data)
    db.close()

 

2.插入数据

import sys
import MySQLOperating

# Inster table datas
def insert_data(table, datas):
    db = MySQLOperating()
    db.clear(table)
    for data in datas:
        db.insert(table, data)
    db.close()

#Create data
table_poll_question = "polls_question"
datas_poll_question =[ {'id': 1, 'question_text': 'you buy pro6?', 'pub_date':'2016-07-23 09:58:56.000000'}]

table_poll_choice = "polls_choice"
datas_poll_choice =[{'id': 1, 'choice_text': 'buy', 'votes': 0, 'question_id': 1},
                   {'id': 2, 'choice_text': 'not buy', 'votes': 0, 'question_id': 1},]


# init data
def init_data():
    insert_data(table_poll_question, datas_poll_question)
    insert_data(table_poll_choice, datas_poll_choice)

if __name__ == '__main__':
    init_data()