25
SQL to Pandas 速查表(二)
source link: https://mp.weixin.qq.com/s?__biz=MzI1MTQwNTY0NA%3D%3D&%3Bmid=2247483697&%3Bidx=1&%3Bsn=b98a0d4efa4bef262b41f61473b77c01&%3Bchksm=e9f23512de85bc04f24a0acc4a7bf3a465a9f26e43487a0e731ae3d23d65a6f57bb2874
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
介绍
此文为 SQL to Pandas 系列第二篇
可先阅读
补充上篇 JOIN 内容
本篇将解构下面的 SQL 查询句式, 使用 Pandas 进行实现
SQL 查询句式
SELECT DISTINCT [字段]
FROM [表] JOIN [bin] ON [连接条件]
WHERE [过滤条件]
GROUP BY [字段]
HAVING [条件]
ORDER BY [字段] DESC
LIMIT [个数] OFFSET [个数]
读取评论数据
df_comments = pd.read_sql(sql="select * from comments", con=conn)
数据预览
df_commentsid student_id content 0 1 1 测试评论1 1 2 5 测试评论5 2 3 2 测试评论2 3 4 3 测试评论3 4 5 1 测试评论11 5 6 9 测试评论9
JOIN
(INNER) JOIN
SQL
SELECT
*
FROM
student
INNER JOIN comments ON student.id = comments.student_id;
Pandas
pd.merge(df, df_comments, left_on='id', right_on='student_id')
LEFT (OUTER) JOIN
SQL
SELECT
*
FROM
student
LEFT JOIN comments ON student.id = comments.student_id;
Pandas
pd.merge(df, df_comments, left_on='id', right_on='student_id', how='left')
RIGHT (OUTER) JOIN
SQL
SELECT
*
FROM
student
RIGHT JOIN comments ON student.id = comments.student_id;
Pandas
pd.merge(df, df_comments, left_on='id', right_on='student_id', how='right')
UNION
SQL
SELECT * FROM student where city ='北京'
UNION
SELECT * FROM student where sex ='男';
Pandas
pd.concat([df[df.city == '北京'], df[df.sex == '男']]).drop_duplicates().reset_index()
UNION ALL
SQL
SELECT * FROM student where city ='北京'
UNION ALL
SELECT * FROM student where sex ='男';
Pandas
pd.concat([df[df.city == '北京'], df[df.sex == '男']]).reset_index()
本篇内容
本篇将解构下面的 SQL 查询句式, 使用 Pandas 进行实现
SQL 创建句式
CREATE TABLE [表名] (
[列名] [类型],
[列名] [类型],
....
);
SQL 插入句式
INSERT INTO [表名] VALUES ([值], [值], ...);
INSERT INTO [表名] ([列名],[列名] ...) VALUES ([值], [值], ...);
SQL 更新句式
UPDATE [表名]
SET [列名] = [值], [列名] = [值]
WHERE [过滤条件];
SQL 删除句式
DELETE FROM [表名] WHERE [过滤条件];
CREATE
SQL
CREATE TABLE student (
id INT ( 11 ) NOT NULL AUTO_INCREMENT,
name VARCHAR ( 10 ) COLLATE utf8mb4_general_ci DEFAULT NULL,
age date DEFAULT NULL,
sex VARCHAR ( 10 ) COLLATE utf8mb4_general_ci DEFAULT NULL,
city VARCHAR ( 255 ) CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
money DOUBLE ( 255, 2 ) DEFAULT NULL,
);
Pandas
pd.DataFrame(columns=['id', 'name', 'sex', 'city', 'money'])
INSERT
SQL
INSERT INTO student (id, name, age, sex, city, money )
VALUES
(1, '张三', '2017-12-20', '女', '天津', 20.00 );
Pandas
# 第一种
df.loc[-1] = [1, '张三', '女', '天津', 20.00]
df.index = df.index + 1
df = df.sort_index()
# 第二种
temp_pd = pd.DataFrame({'id': [1], 'name': ['张三'], 'sex': ['女'], 'city': ['天津'], 'money': [20.00]})
df = pd.concat([df,temp_pd], ignore_index=True)
df.reset_index()
# 第三种
temp_pd = pd.DataFrame([[1,'张三1', '女', '天津', 20.00]], columns=df.columns)
df = pd.concat([df, temp_pd])
df.reset_index()
UPDATE
SQL
UPDATE student SET money = 300 WHERE id = 1;
Pandas
df.loc[df.id == 1, 'money'] = 300
DELETE
SQL
-- 测试时会因外键报错,此处忽略,仅讨论句法
DELETE FROM student WHERE id = 1;
Pandas
df = df.loc[df.id != 1]
本系列文章
听说好看的人才能点
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK