python3操作MySQL数据库

# ======== 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()

 

3.更新数据

 

python3操作MySQL数据库

标签：