python 操作mysql数据库
时间: 2023-09-10 07:11:32 浏览: 44
Python可以通过MySQLdb模块来操作MySQL数据库。
首先需要安装MySQLdb模块,可以通过以下命令进行安装:
```
pip install MySQL-python
```
连接数据库:
```
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 关闭数据库连接
db.close()
```
创建表:
```
import MySQLdb
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")
cursor = db.cursor()
# 使用execute()方法执行SQL语句
cursor.execute("CREATE TABLE employee (id INT, name VARCHAR(20), age INT, sex CHAR(1))")
# 关闭数据库连接
db.close()
```
插入数据:
```
import MySQLdb
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO employee(id, name, age, sex) VALUES (%s, %s, %s, %s)"
# 执行SQL语句
cursor.execute(sql, (1, '张三', 20, 'M'))
# 提交修改
db.commit()
# 关闭数据库连接
db.close()
```
查询数据:
```
import MySQLdb
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM employee WHERE age > %s"
# 执行SQL语句
cursor.execute(sql, (18,))
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
id = row[0]
name = row[1]
age = row[2]
sex = row[3]
# 打印结果
print("id=%s,name=%s,age=%s,sex=%s" % (id, name, age, sex))
# 关闭数据库连接
db.close()
```
更新数据:
```
import MySQLdb
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE employee SET age = age + 1 WHERE sex = %s"
# 执行SQL语句
cursor.execute(sql, ('M',))
# 提交修改
db.commit()
# 关闭数据库连接
db.close()
```
删除数据:
```
import MySQLdb
db = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM employee WHERE age > %s"
# 执行SQL语句
cursor.execute(sql, (30,))
# 提交修改
db.commit()
# 关闭数据库连接
db.close()
```