您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

Python visual data analysis 09. MySQL read and write

編輯：Python

edit

Python Visual data analysis 09、MySQL Reading and writing

Preface
Blog ：【 Red eye aromatherapy blog _CSDN Blog - Computer theory ,2022 Blue Bridge Cup ,MySQL Domain Blogger 】
This article is written by 【 Red eye aromatherapy 】 original , First appeared in CSDN
2022 The greatest wish of the year ：【 Serve millions of technical people 】
Python Initial environment address ：【Python Visual data analysis 01、python Environment building 】

Environmental requirements
Environmental Science ：win10
development tool ：PyCharm Community Edition 2021.2
database ：MySQL5.6

Catalog

Python Visual data analysis 09、MySQL Reading and writing

Preface

Environmental requirements

Pre environment

database

Data sheet

python link MySQL

python operation MySQL Additions and deletions

Pre environment

pip3 config set global.index-url https://repo.huaweicloud.com/repository/pypi/simplepip3 config listpip3 install --upgrade pippip3 install pymysql

edit

database

edit

Data sheet

CREATE TABLE `users` ( `id` int(8) NOT NULL AUTO_INCREMENT, `userName` varchar(255) NOT NULL, `age` int(11) NOT NULL, `introduce` varchar(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

python link MySQL

import pymysql # link MySQLdb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='12345678', db='mytest', charset='utf8')print(db)

edit

python operation MySQL Additions and deletions

import pymysql # link MySQLdb = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='12345678', db='mytest', charset='utf8')cursor = db.cursor() # To write SQLsqlInsert1 = "insert into users values(0,'{0}',{1},'{2}')".format(" Lei Jing ", 22, " Soft as eyes of a clear water ")sqlInsert2 = "insert into users values(0,'{0}',{1},'{2}')".format(" Xiaofeng ", 21, " The little girl ")sqlInsert3 = "insert into users values(0,'{0}',{1},'{2}')".format(" Spring dream ", 20, " Natural and unrestrained domineering ")sqlInsert4 = "insert into users values(0,'{0}',{1},'{2}')".format(" Delete the test ", 20, " Data to be deleted ")cursor.execute(sqlInsert1)cursor.execute(sqlInsert2)cursor.execute(sqlInsert3)cursor.execute(sqlInsert4)sqlUpdate = "update users set introduce='{0}' where userName='{1}'".format(" Handsome girl ", " Spring dream ")cursor.execute(sqlUpdate)sqlDelete = "delete from users where userName='{0}'".format(" Delete the test ")cursor.execute(sqlDelete)# Submit db.commit()sql = "select * from users" # perform SQLcursor.execute(sql) # Go back to the collection data = cursor.fetchall() # Ergodic set for item in data:print(data) # Close database connection db.close()

edit