Mysql python 使用小结

Mysql

MySQL 是一个流行的关系型数据库管理系统，提供了丰富的操作命令来管理数据库。以下是一些 MySQL 操作的基本命令：

1. 连接到 MySQL 服务器：

   mysql -u username -p

这会提示您输入密码来连接到 MySQL 服务器。”username” 是您的 MySQL 用户名。

2. 显示所有数据库：

   SHOW DATABASES;

3. 创建新数据库：

   CREATE DATABASE database_name;

4. 使用数据库：

   USE database_name;

5. 显示当前使用的数据库中的所有表：

   SHOW TABLES;

6. 创建新表：

   CREATE TABLE table_name (
       column1 datatype constraints,
       column2 datatype constraints,
       ...
   );

7. 显示表的结构：

   DESCRIBE table_name;

8. 插入数据到表中：

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

9. 查询表中的数据：

   SELECT * FROM table_name;

10. 更新表中的数据：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
11. 删除表中的数据：DELETE FROM table_name WHERE condition; DELETE FROM bookreview_info WHERE id > 227247;
12. 删除表：
    DROP TABLE table_name; DROP TABLE bookreview_info;

这些是 MySQL 中的一些基本操作命令，您可以使用它们来管理数据库和表，执行数据操作和查询。请注意，在执行任何更改数据的操作之前，请谨慎，并确保备份重要的数据。

2. df to mysql

import pandas as pd
import pymysql
from sqlalchemy import create_engine
 
 
 
# MySQL 数据库连接信息
host = "192.168.1.100"
user = "root"
password = "u22"
database = "doubanbook"
table_name = "bookreview"
 
# 建立数据库连接
engine = create_engine(f"mysql+pymysql://{user}:{password}@{host}/{database}")
 
# 将 DataFrame 写入 MySQL 数据库表，如果表不存在则会创建
df.to_sql(table_name, engine, if_exists="replace", index=False)

# 建表
CREATE TABLE your_table_name (
    ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    time DATE,
    user_id INT,
    book_id INT,
    rating INT,
    labels VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    comment TEXT
);
 
CREATE TABLE `bookreview_info` (
  `id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  `time` VARCHAR(16) DEFAULT NULL COMMENT '评论时间',
  `user_id` INT DEFAULT NULL COMMENT '用户 id',
  `book_id` INT DEFAULT NULL COMMENT '书籍 id',
  `rating` TINYINT UNSIGNED DEFAULT NULL COMMENT '评分',
  `labels` TEXT DEFAULT NULL COMMENT '标签',
  `comment` TEXT DEFAULT NULL COMMENT '评论',
  UNIQUE KEY `index_unique` (`user_id`, `book_id`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 插入数据

host = '112.122.1.10'  # MySQL 主机名
user = 'root'  # MySQL 用户名
password = 'root'  # MySQL 密码
database = 'table'  # 数据库名
 
connection = pymysql.connect(host=host,
                             user=user,
                             password=password,
                             db=database,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
 
try:
    # 创建游标对象
    with connection.cursor() as cursor:
        # 逐行插入数据
        for _, row in df.iterrows():
            sql = f"INSERT INTO bookreview_info (`id`, `time`, `user_id`, `book_id`, `rating`, `labels`, `comment`) VALUES ({row['ID']},'{row['time']}', {row['user_id']}, {row['book_id']}, {row['rating']},'{row['labels']}','{row['comment']}')"
            cursor.execute(sql)
 
    # 提交事务
    connection.commit()
 
finally:
    # 关闭连接
    connection.close()

或者：

from sqlalchemy import create_engine
 
 
engine = create_engine('mysql+pymysql://root:root@112./table')
 
table_name = 'table_name'
 
df.to_sql(name=table_name, con=engine, if_exists='append', index=False)
 
###
table_name = 'table_name'
existing_ids = pd.read_sql(f"SELECT id FROM {table_name}", con=engine)['id'].tolist()
 
df_to_insert = df[~df['id'].isin(existing_ids)]
df_to_insert.to_sql(name=table_name, con=engine, if_exists='replace', index=False)

4. DB 类方便操作

import pymysql
from dbutils.pooled_db import PooledDB
 
 
class DBHelper(object):
 
    def __init__(self):
        # TODO 此处配置，可以去配置文件中读取。self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0 和 None 表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0 表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0 和 None 不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL 服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
)
 
    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor
 
    def close_conn_cursor(self, *args):
        for item in args:
            item.close()
 
    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
 
        cursor.execute(sql, kwargs)
        conn.commit()
 
        self.close_conn_cursor(conn, cursor)
 
    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
 
        cursor.execute(sql, kwargs)
        result = cursor.fetchone()
 
        self.close_conn_cursor(conn, cursor)
        return result
 
    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
 
        cursor.execute(sql, kwargs)
        result = cursor.fetchall()
 
        self.close_conn_cursor(conn, cursor)
 
        return result
 
 
db = DBHelper()
 
####################################
from db import db
 
db.exec("insert into d1(name) values(%(name)s)", name="武沛齐 666")
 
ret = db.fetch_one("select * from d1")
print(ret)
 
ret = db.fetch_one("select * from d1 where id=%(nid)s", nid=3)
print(ret)
 
ret = db.fetch_all("select * from d1")
print(ret)
 
ret = db.fetch_all("select * from d1 where id>%(nid)s", nid=2)
print(ret)