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、
推荐阅读