python_python连接至MySQL

最新推荐文章于 2024-10-12 11:36:49 发布

迷失的√3

最新推荐文章于 2024-10-12 11:36:49 发布

阅读量6.9k

点赞数 8

分类专栏： Python

Python 专栏收录该内容

63 篇文章 1 订阅

订阅专栏

PyMySQL

MySQL 可应用于多种语言，可以使用Python来连接和操作MySQL数据库

什么是 PyMySQL？

1、PyMySQL 是在 Python3.x 版本中用于连接 MySQL 数据库的一个python包

Python2中则使用mysqldb

2、在使用 PyMySQL 之前，我们需要确保 PyMySQL 已安装

1、pycharm 安装
2、pip install PyMySQL（详情参考可见PIP安装）

PyMySQL的使用方法

import pymysql

-- 创建连接
conn = pymysql.connect("localhost","root","123456","mydb")

-- 使用 cursor() 方法创建游标，用于执行sql语句并获得结果
cursor = conn.cursor()

-- 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")

-- 关闭连接
conn.close()

插入数据

#增加数据
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   cursor.execute(sql)
   # 提交当前游标的全部操作
   conn.commit()
except:
   conn.rollback()

获取查询结果

# 执行查询
insert_sql = "select * from class"
cursor.execute(insert_sql)

# 获取单条
one_data = cursor.fetchone()
print(one_data)
print("一条数据获取完毕")

# 获取多条
many_data = cursor.fetchmany(num)
print(many_data)
print("many数据获取完毕")

# 获取剩余全部
all_data = cursor.fetchall()
print(all_data)
print("all数据获取完毕")
for i in all_data:
    print(i)
    print(i[0])
    print(i[1])
    print(i[2])
    print(i[3])
    print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % (fname, lname, age, sex, income )

更新数据

update_sql = 'update stu set name="明明" where id =2'
try:
    cursor.execute(update_sql)
    print('sql执行完毕')
    conn.commit()
    print('sql提交完毕')
except:
   conn.rollback()

删除数据

delete_sql = "delete from stu where id =2"
try:
   cursor.execute(delete_sql)
   conn.commit()
except:
   conn.rollback()

关闭连接及游标

cursor.close()
conn.close()

练习

import pymysql
import traceback
from time import sleep

class PyMySQL(object):
    create_table = 'create table stu(id int not null primary key auto_increment,name varchar(255) not null,age int, sex varchar(255))default charset=utf8'
    select = 'select * from class'
    update = 'update stu set name="明明" where id=2'
    delete = 'delete from stu where id=9'
    insert = 'insert into stu(name,age,sex) values("%s","%d","%s")' % ('小明', 2, "男")

    def __init__(self, host, user, pwd, db):
        self.conn = pymysql.connect(host, user, pwd, db)
        self.cursor = self.conn.cursor()

    def create_table_func(self):
        self.cursor.execute(PyMySQL.create_table)
        print('数据表创建完毕')

    def insert_date(self):
        try:
            self.cursor.execute(PyMySQL.insert)
            self.conn.commit()
        except:
            print(traceback.format_exc())
            self.conn.rollback()

    def update_data(self):
        try:
            self.cursor.execute(PyMySQL.update)
            self.cursor.commit()
        except:
            print(traceback.format_exc())
            self.conn.rollback()

    def delete_data(self):
        try:
            self.cursor.execute(PyMySQL.delete)
            self.conn.commit()
        except:
            print(traceback.format_exc())
            self.conn.rollback()
            
    def select_data(self):
        self.cursor.execute(PyMySQL.select)

        all_data = self.cursor.fetchall()
        for i in all_data:
            print('查询结果为：{}'.format(i))

if __name__ == '__main__':
    my = PyMySQL('localhost','root','123456','python21_test')
    my.create_table_func()
    my.insert_date()
    my.update_data()
    my.delete_data()
    my.select_data()