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()
总结:如果是插入带变量的字段,写法要注意(加上单引号):
abc = "我会努力"
ggo = 9588
sql2 = F"INSERT INTO t_yjw(tag_name, n_num) VALUES ('{abc}', '{ggo}')"
cursor.execute(sql2)
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、
# cursorclass=pymysql.cursors.DictCursor
# 与
# cursor=pymysql.cursors.DictCursor
# 的用法及区别
#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 查询数据库的某一张表
# cursorclass=pymysql.cursors.DictCursor
# 与
# cursor=pymysql.cursors.DictCursor
# 的用法及区别
import pymysql
db = ""
def mysql_conn():
global db
try:
# 打开数据库连接
# db = pymysql.connect("localhost", "root", "dong2025", "mysql8", charset='utf8')
db = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="dong2025",
database="mysql8",
charset="utf8",
# cursorclass=pymysql.cursors.DictCursor # 可以返回字典数据(包含字段名字),
)
# 使用cursor()方法获取操作游标
# cursor = db.cursor()
cursor = db.cursor(cursor=pymysql.cursors.DictCursor) # 可以返回字典数据(包含字段名字)
# 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)
5.1,pymysql,pandas,mysql一起来操作数据库
import pandas as pd
import pymysql
db = ""
def mysql_conn():
global db
try:
# 打开数据库连接
db = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="dong2025",
database="mysql8",
charset="utf8",
)
sql = "SELECT * FROM BOOK"
df_sql = pd.read_sql(sql, db)
# return df_sql # 展示所有结果
# return df_sql.head() # 默认展示前5行
# return df_sql.head(2) # 展示前2行
# return df_sql.shape # 统计行和列数。
return df_sql.info() # info()方法可以查看数据表中的数据类型
except:
# Rollback in case there is any error
db.rollback()
finally:
db.close()
my_con = mysql_conn()
print(my_con)
5.2、pymysql,pandas,mysql一起来操作数据库,并保存在excel中
import pandas as pd
import pymysql
db = ""
def mysql_conn():
global db
try:
# 打开数据库连接
db = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="dong2025",
database="mysql8",
charset="utf8",
)
sql = "SELECT * FROM BOOK"
df_sql = pd.read_sql(sql, db)
# return df_sql # 展示所有结果
return df_sql.head() # 默认展示前5行
# return df_sql.head(2) # 展示前2行
# return df_sql.shape # 统计行和列数。
# return df_sql.info() # info()方法可以查看数据表中的数据类型
except:
# Rollback in case there is any error
db.rollback()
finally:
db.close()
my_cons = mysql_conn()
my_cons.to_excel('C:/Users/Administrator/Desktop/456999/python_mysql.xlsx')
print(my_cons)
5.3、创建一个字典,把数据放在字典里,字段名称是key,字段内容是values
pymysql,pandas,mysql一起来操作数据库,并保存在excel中
import pandas as pd
import pymysql
db = ""
def mysql_conn():
global db
try:
# 打开数据库连接
db = pymysql.connect(
host="localhost",
port=3306,
user="root",
password="dong2025",
database="mysql8",
charset="utf8",
)
sql = "SELECT * FROM BOOK"
df_sql = pd.read_sql(sql, db)
# return df_sql # 展示所有结果
return df_sql.head() # 默认展示前5行
# return df_sql.head(2) # 展示前2行
# return df_sql.shape # 统计行和列数。
# return df_sql.info() # info()方法可以查看数据表中的数据类型
# return df_sql.describe() # 对BOOK表的描述
except:
# Rollback in case there is any error
db.rollback()
finally:
db.close()
my_con = mysql_conn()
my_con.to_excel("C:/Users/Administrator/Desktop/123456/202201101647.xlsx")
print(my_con)
print("***************************")
# 创建一个字典,把数据放在字典里,字段名称是key,字段内容是values
t_dict = {}
for x, y in my_con.items():
print("x=", x, "y=", list(y.values))
t_dict[x] = list(y.values)
print("t_dict=", t_dict)
print("**********************")
# 根据字段名称取数据
print(my_con["book_name"] + " " + my_con["writer"])
7、Python连接MySQL数据库方法介绍(超详细!手把手项目案例操作) - 哔哩哔哩https://www.bilibili.com/read/cv3418619
8、Python3下不同MySQL驱动的性能对比_Python小屋-CSDN博客
9、
mysql客户端pymysql在python下性能比较_得救之道就在其中-CSDN博客
10、
11、
12、
13、Python之安装MySQLdb遇到的问题
14、Python_数据结构_字典和集合的差异对比
15、在Python中分词
在Python中分词_程序鸡的博客-CSDN博客https://blog.csdn.net/weixin_44611096/article/details/109694186
16、如何用python将数据写入Excel文件中
17、读excel的时候有乱码后面要加上 .astype(str)
# !/usr/bin/python # -*- coding: UTF-8 -*- # SQL 插入语句 import MySQLdb import pandas as pd import xlwt import jieba df = pd.read_excel("C:/Users/jack2035/Desktop/abc.xlsx", sheet_name="Sheet1").astype(str) # print(df) pdd = pd.DataFrame(df) sts = "" db = "" # 检验是否全是中文字符 def isAllChinese(s): for c in s: if not ('\u4e00' <= c <= '\u9fa5'): return False return True def mysql_conn(sts, n_num): global db try: # 打开数据库连接 db = MySQLdb.connect("localhost", "root", "123456", "test_sql", charset='utf8') # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = F"INSERT INTO t_yjw(tag_name, n_num) VALUES ('{sts}', '{n_num}')" # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # Rollback in case there is any error db.rollback() finally: db.close() sf_list = [] for i_num in range(len(pdd["地址"])): for i_li in list(jieba.cut(pdd["地址"][i_num], cut_all=True)): if isAllChinese(i_li) and len(i_li) > 1: sf_list.append(i_li) # print(i_li) else: pass # print(sf_list) # 定义字典用来排序 # 定义集合用来过滤重复 set_st = set() dc_dict = {} for ts in sf_list: set_st.add(ts) # print(set_st) for i_se in set_st: # 执行insert语句的函数 mysql_conn(i_se, sf_list.count(i_se)) # print(i_se, "=", sf_list.count(i_se)) # dc_dict[i_se] = sf_list.count(i_se) # 对字典值进行排序(倒序) # re_dic = sorted(dc_dict.items(), key=lambda x: x[1], reverse=True) # 输出结果 # for k, v in dict(re_dic).items(): # print(k, v) # mysql_conn("努力", 999)
18、
19、
20、
21、
22、
23、
24、