python 与 mysql 连接（增删改查）_Jack

1、

查询数据库的某一张表

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#  查询数据库的某一张表
import MySQLdb
 
db = ""
 
 
def mysql_conn():
    global db
    try:
        # 打开数据库连接
        db = MySQLdb.connect("localhost", "root", "dong2025", "mysql8", charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 查询数据库中的某一张表
        sql = "SELECT * FROM BOOK WHERE BOOK_ID > 3"
        # 执行sql语句
        cursor.execute(sql)
        # fetchall():接收全部的返回结果行.
        # alldata = cursor.fetchall()
        # return alldata
        # fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
        onedate = cursor.fetchone()
        return onedate
 
 
    except:
        # Rollback in case there is any error
        db.rollback()
    finally:
        db.close()
 
 
my_con = mysql_conn()
print(my_con)
 

2、

SQL 插入语句

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#  SQL 插入语句
import MySQLdb
 
db = ""
 
 
def mysql_conn():
    global db
    try:
        # 打开数据库连接
        db = MySQLdb.connect("localhost", "root", "dong2025", "mysql8", charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 插入语句
        sql = """
                 INSERT INTO BOOK(book_id, SORT, book_name, writer, OUTPUT, price)
                 VALUES (11, 'T8988', '数据科学家', '作者_李克强', '深圳出版社', 666.00)
              """
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()
    finally:
        db.close()
 
 
mysql_conn()
 
 

3、

 SQL update更新语句

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#  SQL update更新语句
import MySQLdb
 
db = ""
 
 
def mysql_conn():
    global db
    try:
        # 打开数据库连接
        db = MySQLdb.connect("localhost", "root", "dong2025", "mysql8", charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL update更新语句
        sql = "UPDATE BOOK SET book_name = 'python算法导论' WHERE book_id= 9"
        print(sql)
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()
    finally:
        db.close()
 
 
mysql_conn()
 
 

4、

SQL 删除语句

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#  SQL 删除语句
import MySQLdb
 
db = ""
 
 
def mysql_conn():
    global db
    try:
        # 打开数据库连接
        db = MySQLdb.connect("localhost", "root", "dong2025", "mysql8", charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL delete语句
        str_num = 8
        sql = F"DELETE FROM BOOK  WHERE book_id= {str_num}"
        print(sql)
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # Rollback in case there is any error
        db.rollback()
    finally:
        db.close()
 
 
mysql_conn()
 
 

5、

6、

7、

8、