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就行了啊