Python 对Mysql数据库的操作 - 实验

1：今天当我尝试向数据库里面插入数据：

先看一下 database 里面的database:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.03 sec)

mysql> create database testdb;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| testdb |

+--------------------+

5 rows in set (0.01 sec)

mysql> use testdb;

Database changed

mysql> show tables;

Empty set (0.00 sec)

2：今天当我尝试用python向数据库里面插入数据

DB_employee_create.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost", "root", "Sheng2020", "testdb", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()

看pycharm 里面return code is 0. success.

mysql> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| EMPLOYEE |

+------------------+

1 row in set (0.01 sec)

mysql>

3: 下面向table 里面插入数据：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost", "root", "Sheng2020", "testdb", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# 关闭数据库连接
db.close()

查询一下：

mysql> select * from EMPLOYEE;

+------------+-----------+------+------+--------+

| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |

+------------+-----------+------+------+--------+

| Mac | Mohan | 20 | M | 2000 |

+------------+-----------+------+------+--------+

1 rows in set (0.01 sec)

mysql>