Mysql
MySQL 是一个流行的关系型数据库管理系统,提供了丰富的操作命令来管理数据库。以下是一些 MySQL 操作的基本命令:
- 连接到 MySQL 服务器:
mysql -u username -p
这会提示您输入密码来连接到 MySQL 服务器。”username” 是您的 MySQL 用户名。
- 显示所有数据库:
SHOW DATABASES;
- 创建新数据库:
CREATE DATABASE database_name;
- 使用数据库:
USE database_name;
- 显示当前使用的数据库中的所有表:
SHOW TABLES;
- 创建新表:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
- 显示表的结构:
DESCRIBE table_name;
- 插入数据到表中:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- 查询表中的数据:
SELECT * FROM table_name;
- 更新表中的数据:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
- 删除表中的数据:
DELETE FROM table_name WHERE condition; DELETE FROM bookreview_info WHERE id > 227247;
- 删除表:
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)
正文完