python3操作MySQL数据库
时间：2021-07-01 10:21:17
帮助过：2人阅读
# ======== 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数据库
标签：
