python简易操作数据库(MySQL)

连接数据库

首先我们需要连接本地MySQL服务

[root@host]# mysql -u root -p
Enter password:******

查看dataset

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

创建dataset

mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+

安装mysql.connector

使用 mysql-connector 来连接使用 MySQL， mysql-connector 是 MySQL 官方提供的驱动器

PS C:\Users\qaz\Desktop> pip install mysql-connector -i https://pypi.tuna.tsinghua.edu.cn/simple
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting mysql-connector
  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/28/04/e40098f3730e75bbe36a338926f566ea803550a34fb50535499f4fc4787a/mysql-connector-2.2.9.tar.gz (11.9 MB)
     |████████████████████████████████| 11.9 MB 3.2 MB/s
Building wheels for collected packages: mysql-connector
  Building wheel for mysql-connector (setup.py) ... done
  Created wheel for mysql-connector: filename=mysql_connector-2.2.9-cp37-cp37m-win_amd64.whl size=247952 sha256=61e6ee764f44d9ffd142699174d8a7d4c36f7c350bf5d18a6f86caa7ce728e57
  Stored in directory: c:\users\qaz\appdata\local\pip\cache\wheels\06\97\d1\5754eced54f229377ae39c2261cc521656cc66b3d73c6361a0
Successfully built mysql-connector
Installing collected packages: mysql-connector
Successfully installed mysql-connector-2.2.9
WARNING: You are using pip version 20.2.1; however, version 20.2.4 is available.
You should consider upgrading via the 'c:\users\qaz\appdata\local\programs\python\python37\python.exe -m pip install --upgrade pip' command.
PS C:\Users\qaz\Desktop> 

文中的错误时版本信息问题 可以根据提示更新pip版本
python.exe -m pip install --upgrade pip

导入mysql.connector

官网使用教程

import mysql.connector

创建数据库连接

config = {
	'host': 'localhost',		# 数据库主机地址
	'user': 'root',				# 数据库用户名
  	'password': '123',			# 数据库密码
	'database': "TEST_DB",		# database
}
mydb = mysql.connector.connect(**cofig)

# 关闭连接
mydb.close()

如果以上代码遇到错误
不妨将mysql.connector库卸载重新安装一下
pip install mysql-connector-python

查看databases

# All DDL (Data Definition Language) statements are executed using a handle structure known as a cursor.
mycursor = mydb.cursor()

# execute()可以执行mysql语言
mycursor.execute("SHOW DATABASES")
# 打印查询结果
print(mycursor.fetchall())

mycursor.close()

更新数据表

# Make sure data is committed to the database
mydb.commit()

MySQL语法

• 创建数据库
  create database 数据库名;
• 删除数据库
  drop database <数据库名>;
• 进入数据库
  use <数据库名>
• 创建teble
  create table 数据表名(列名称1 数据类型, 列名称2 数据类型, )
• 检索数据
  select 列名 from 表名;
• 添加数据
  insert into 表名 (列名, ) values (值, )
• 查看数据
  select 列名 from 表名

遇到的问题

unread result found 释放cursor获取数据 使用fetchall()函数