Mysql python 使用小结

Mysql

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

  1. 连接到 MySQL 服务器:
   mysql -u username -p

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

  1. 显示所有数据库:
   SHOW DATABASES;
  1. 创建新数据库:
   CREATE DATABASE database_name;
  1. 使用数据库:
   USE database_name;
  1. 显示当前使用的数据库中的所有表:
   SHOW TABLES;
  1. 创建新表:
   CREATE TABLE table_name (
       column1 datatype constraints,
       column2 datatype constraints,
       ...
   );
  1. 显示表的结构:
   DESCRIBE table_name;
  1. 插入数据到表中:
   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
  1. 查询表中的数据:
   SELECT * FROM table_name;
  1. 更新表中的数据:UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  2. 删除表中的数据:DELETE FROM table_name WHERE condition; DELETE FROM bookreview_info WHERE id > 227247;
  3. 删除表:
    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)
正文完
 
admin
版权声明:本站原创文章,由 admin 2023-11-26发表,共计4280字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请联系tensortimes@gmail.com。
评论(没有评论)
验证码