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()
posted @ 2016-07-24 23:28  hjhsysu  阅读(398)  评论(0编辑  收藏  举报