python 使用 mysql 驱动mysqlclient 操作-马育民老师

# 导入模块

```
import MySQLdb
```

# 连接数据库

```
conn = MySQLdb.connect(
    host="127.0.0.1",
    port=3306,
    db="db2402",
    user="root",
    password="123456",
    charset='utf8')
```

# 查询数据

```
import MySQLdb

conn = MySQLdb.connect(
    host="127.0.0.1",
    port=3306,
    db="db2402",
    user="root",
    password="123456",
    charset='utf8')
# 获取游标
cursor = conn.cursor()

sql = "select id,name,sex,phone from t_student"
# 通过游标执行sql
cursor.execute(sql)
# 获取所有查询结果
ls = cursor.fetchall()

# print("ls:",ls)
for item in ls:
    print(item)

cursor.close()
conn.close()
```

# 执行insert语句

```
import MySQLdb

conn = MySQLdb.connect(
    host="127.0.0.1",
    port=3306,
    db="db2402",
    user="root",
    password="123456",
    charset='utf8')

# 通过数据库连接获取游标
cursor = conn.cursor()

sql = "insert into t_student(id,name,sex,phone) values (4,'lili','女','138')"
# 通过游标执行sql
cursor.execute(sql)
# 关闭索引
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
```

# 执行update语句

```
import MySQLdb

conn = MySQLdb.connect(
    host="127.0.0.1",
    port=3306,
    db="db2402",
    user="root",
    password="123456",
    charset='utf8')

# 通过数据库连接获取游标
cursor = conn.cursor()

sql = "update t_student set phone = '137777' where name = '韩梅梅'"
# 通过游标执行sql
cursor.execute(sql)
# 关闭索引
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
```

# 执行delete语句

```
import MySQLdb

conn = MySQLdb.connect(
    host="127.0.0.1",
    port=3306,
    db="db2402",
    user="root",
    password="123456",
    charset='utf8')

# 通过数据库连接获取游标
cursor = conn.cursor()

sql = "delete from t_student where name = '李雷' "
# 通过游标执行sql
cursor.execute(sql)
# 关闭索引
cursor.close()
# 提交事务
conn.commit()
# 关闭数据库连接
conn.close()
```

# 封装类

```
import MySQLdb

class MySQLUtil:

def __init__(self):
        self.conn = None

def connect(self,host,port,db,user,password,charset='utf8'):
        '''
        连接数据库
        :return:
        '''
        self.conn = MySQLdb.connect(
            host=host,
            port=port,
            db=db,
            user=user,
            password=password,
            charset=charset)

def update(self,sql):
        '''
        执行增删改sql
        :param sql:
        :return:
        '''
        cursor = self.conn.cursor()
        cursor.execute(sql)
        cursor.close()
        self.conn.commit()

def query(self,sql):
        '''
        执行查询
        :param sql:
        :return:
        '''
        cursor = self.conn.cursor()
        cursor.execute(sql)
        return cursor.fetchall()

def close(self):
        '''
        关闭数据库连接
        :return:
        '''
        self.conn.close()

```

### 测试查询

```
sql = "select id,name,sex,phone from t_student"
util = MySQLUtil()
util.connect('127.0.0.1',3306,'db2402','root','123456')
ls = util.query(sql)
util.close()
for item in ls:
    print(item)
```

### 测试插入数据

```
sql = '''
insert into t_student (id,name,sex,phone) values (5,'王钢蛋','女','136666')
'''
util = MySQLUtil()
util.connect('127.0.0.1',3306,'db2402','root','123456')
util.update(sql)
util.close()
```

原文出处：http://malaoshi.top/show_1GW1I7EdxJc7.html