SQL1
/*
Navicat MySQL Data Transfer
Target Server Type : MYSQL
Target Server Version : 50629
File Encoding : 65001
Date: 2016-08-26 14:27:58
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student1
-- ----------------------------
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`dormitory` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student1
-- ----------------------------
INSERT INTO `student1` VALUES ('0', '路人甲', '宿舍A');
INSERT INTO `student1` VALUES ('1', '宋人乙', '宿舍B');
INSERT INTO `student1` VALUES ('2', '土匪丁', '宿舍C');
INSERT INTO `student1` VALUES ('3', '宝宝', '宿舍A');
SQL2
/*
Navicat MySQL Data Transfer
Target Server Type : MYSQL
Target Server Version : 50629
File Encoding : 65001
Date: 2016-08-26 14:28:08
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for student2
-- ----------------------------
DROP TABLE IF EXISTS `student2`;
CREATE TABLE `student2` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student2
-- ----------------------------
INSERT INTO `student2` VALUES ('0', '路人甲', '13800138000');
INSERT INTO `student2` VALUES ('1', '宋人乙', '13800138111');
INSERT INTO `student2` VALUES ('2', '土匪丁', '13800138222');
INSERT INTO `student2` VALUES ('3', '宝宝', '1111111111');
输出SQL语句
SELECT
student1.`name`,
student2.phone,
student1.dormitory
FROM
student1
INNER JOIN student2 ON student1.id = student2.id
WHERE
student1.dormitory = "宿舍A"
ORDER BY
student1.dormitory DESC
问题点
在python中 如何执行mysql的数据,并且保存为表格
import pandas as pd
import numpy as np
import MySQLdb
conn= MySQLdb.connect(
host='localhost',
port = 3306,
user='root',
passwd='root',
db ='root',
charset='utf8'
)
with conn:
cur = conn.cursor()
cur.execute("INSERT INTO......")
cur.close()
conn.commit()
conn.close()
用pandas.DataFrame.to_csv
转成csv就行了啊