嵌入式编程

嵌入式编程

PyMySQL 是一个纯 Python 实现的 MySQL 客户端操作库，支持事务、存储过程、批量执行等。

建立数据库连接

db = pymysql.connect(host = 'localhost', user = 'root', password = '', db = 'test')

执行SQL语句

cursor = db.cursor() #创建游标
sqlstr = """
INSERT INTO student VALUES ('2002','liming','2020-01-01','male')
"""
try:
    cursor.execute(sqlstr.lower())
    cursor.execute('select * from student;')
    results = cursor.fetchall()
    for row in results:
        print (row)
except Exception  as e:
    print ("Error: unable to fetch data", e)

为什么要使用游标？

　　游标是系统为用户开辟的一个数据缓冲区，用来存放SQL语句产生的记录，然后将产生的记录逐条的赋值给主语句，交给主语句进行处理。

1. SQL语句是面向集合的，执行一条SQL可能会产生多条记录。
2. 主语言是面向记录的执行一条语句只能产生一条记录。
3. 如果只使用主变量的话，就不能将SQL产生的记录通过主语言进行输出。

关闭数据库连接

db.commit()
db.close()

为什么要使用db.commit()语句？

下面是官方给出的解释：

This method sends a COMMIT statement to the MySQL server, committing the current transaction. Since by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

大概的意思是说：db.commit()会向MySQL服务器发送一个提交命令，提交当前的事务。使用Python嵌入式编程默认情况下并不会自动提交事务，如果数据表中的数据发生更改，对于事务存储引擎（InnoDB）执行这一条语句是很有必要的。

如果不执行这一条语句那么嵌入式编程对数据表所做的修改并不会更改数据库中的数据。

 

习题

【表名和字段】

–学生表

student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别

–课程表

course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号

–成绩表

score(s_id,c_id,s_score) –学生编号,课程编号,分数

使用嵌入式SQL对学生-课程数据库完成下述功能：

1. 插入一个新学生（'2001','wangyan','2000-01-01','女')

import pymysql
db = pymysql.connect(host = 'localhost', user = 'root', password = '', db = 'test')
cursor = db.cursor()
sqlstr = """
INSERT INTO student VALUES ('2002','liming','2020-01-01','male')
"""
try:
    cursor.execute(sqlstr.lower())
    cursor.execute('select * from student;')
    results = cursor.fetchall()
    for row in results:
        print (row)
except Exception  as e:
    print ("Error: unable to fetch data", e)
db.commit()
db.close()

 

2. 修改学号为‘2001’的学生姓名为'wangshan'

#include <iostream>
#include <stdlib.h>
#include <iostream>
#include <string>
#include <stdio.h>
#pragma comment(lib, "libmysql.lib")
#include "C:\xampp\mysql\CPPConnector\include\mysql.h"

using namespace std;

MYSQL my_connect;

void mysql_connect() {
    mysql_init(&my_connect);//初始化
    if (mysql_real_connect(&my_connect, "localhost", "root", "", "test", 3306, NULL, 0)) {
        cout << "连接成功！" << endl;
    }
    else {
        cout << "连接失败：" << mysql_error(&my_connect) << endl;
    }
}

void mysql_close() {
    mysql_close(&my_connect);
}

int main()
{
    mysql_connect();
    char sql[100] = "update student set s_name='wangshang' where s_id='2001'";
    mysql_query(&my_connect, sql);
    mysql_close();
    return 0;
}

3. 用create语句创建新表s2(sno,sname,ssex,sbirth,sphone),字段类型根据语义自行定义。

#include "jdbc/mysql_connection.h"
#include "jdbc/mysql_driver.h"
#include "jdbc/cppconn/statement.h"

#include <iostream>

using namespace std;

int main() {
    //初始化驱动
    try {
        sql::mysql::MySQL_Driver* driver = NULL;
        sql::Connection* con = NULL;
        sql::Statement* stmt;
        sql::ResultSet* res;
        driver = sql::mysql::get_mysql_driver_instance();
        if (driver == NULL)
        {
            cout << "driver is null" << endl;
        }
        con = driver->connect("tcp://localhost:3306/test", "root", "");
        if (con == NULL)
        {
            cout << "conn is null" << endl;
        }
        cout << "connect suceess" << endl;
        //con->setSchema("test");
        stmt = con->createStatement();
        stmt->executeQuery("create table s3 (sno varchar(20) primary key, sname varchar(20), ssex varchar(2), sbirth date, sphone varchar(20));");
        delete res;
        delete stmt;
        delete con;
    }
    catch (sql::SQLException &e) {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
        cout << "# ERR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    }

    return 0;
}