连接数据库
首先我们需要连接本地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()
函数