Python中文网 - 问答频道, 解决您学习工作中的Python难题和Bug
Python常见问题
环境：
Python 3.7 Mysql InnoDB
我试图从不同的表中收集数据。 我有4张桌子：
- 任务,
- 类别
- 类型_任务
- 平台
当我使用Python执行SQL请求时，我只得到3列，而不是6列：
import mysql.connector
"""
# Get list of tasks with:
- id (from W551je5v_pb_tasks)
- name (from W551je5v_pb_tasks)
- introduction (from W551je5v_pb_tasks)
- name_platform (from W551je5v_pb_platforms)
- name_type_tasks (from W551je5v_pb_type_tasks)
- category (from W551je5v_pb_categories)
"""
# ========================= MAKE A FUNCTION TO GET THE MYSQL CONNECTION ==========================================
import mysql
def get_mysql_connection():
"""
THis function will return the connection and cursor of our Mysql database
"""
while True:
try:
mysql_connection = mysql.connector.connect(
host="1.1.1.1",
port=3306,
user="reader",
passwd="123456",
database="db"
)
mysql_cursor = mysql_connection.cursor(dictionary=True)
break
except Exception as ex:
print(
f"Problem with Mysql Database!\n{ex}\nIt is certainly a problem of Internet connexion. PhoneBot will retry to run in 15 seconds.")
return mysql_connection, mysql_cursor
mysql_connection, mysql_cursor = get_mysql_connection()
SQL_DETAILS_OF_TASKS = f"SELECT tasks.id, tasks.name, tasks.introduction, platforms.name, type_tasks.name, categories.name \
FROM W551je5v_pb_tasks AS tasks \
INNER JOIN W551je5v_pb_platforms AS platforms \
ON platforms.id = tasks.id_platform \
INNER JOIN W551je5v_pb_type_tasks AS type_tasks \
ON tasks.id_type_task = type_tasks.id \
INNER JOIN W551je5v_pb_categories AS categories \
ON platforms.id_category = categories.id \
WHERE tasks.enable=1"
mysql_cursor.execute(SQL_DETAILS_OF_TASKS)
tuple_all_tasks_user = mysql_cursor.fetchall()
print(tuple_all_tasks_user)
for task in tuple_all_tasks_user:
#print(f"{task} - {type(task)}")
for column in task:
#print(f"{column} - {type(column)}")
pass
输出：
[{'id': 7, 'name': 'Cold Messaging', 'introduction': '<p>Hello</p>'},
{'id': 8, 'name': 'Cold Messaging 2', 'introduction': '<p>Hello 2</p>'},
{'id': 8, 'name': 'Cold Messaging 3', 'introduction': '<p>Hello 3</p>'}]
如您所见，输出不显示platforms.name、type_tasks.name和categories.name列。 因此，我在Phpmyadmin中复制粘贴了我的SQL请求，并成功地获得了输出中的所有列
我检查了表中的外键，检查了表中是否有innoDB。我还缺什么？ 我更改了管理员mysql用户的凭据，以检查这是否是一个具有某种权限的问题，但得到了相同的结果
我使用Wamp/Phpmyadmin导出并导入本地mysql服务器中的表。对于Mysql的root访问，我也遇到了同样的问题。 但是如果我在phpmyadmin页面中执行SQL，它就会工作
有谁能从他这边重现这个问题吗
对我来说，这似乎是python而不是SQL的问题，不是吗
有人遇到过类似的事情吗
以下是要重现问题的SQL表：
-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le : sam. 22 mai 2021 à 11:26
-- Version du serveur : 8.0.21
-- Version de PHP : 7.3.21
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Base de données : `db_php`
--
-- --------------------------------------------------------
--
-- Structure de la table `w551je5v_pb_categories`
--
DROP TABLE IF EXISTS `w551je5v_pb_categories`;
CREATE TABLE IF NOT EXISTS `w551je5v_pb_categories` (
`ID` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(200) DEFAULT NULL,
`slug` varchar(50) NOT NULL,
`icon` varchar(300) DEFAULT NULL,
`icon_blue_img` varchar(300) DEFAULT NULL,
`icon_black_img` varchar(300) DEFAULT NULL,
`icon_white_img` varchar(300) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `w551je5v_pb_categories`
--
INSERT INTO `w551je5v_pb_categories` (`ID`, `name`, `slug`, `icon`, `icon_blue_img`, `icon_black_img`, `icon_white_img`) VALUES
(1, 'Cold Messaging', 'cold_messaging', '<i class=\"mdi mdi-message-text-outline menu-icon\"></i>', '', '', ''),
(2, 'Influencers', 'influencers', '<i class=\"mdi mdi-human-greeting menu-icon\"></i>', '', '', ''),
(3, 'Scraping', 'scraping', '<i class=\"mdi mdi-database menu-icon\"></i>', '', '', ''),
(4, 'Authority', 'authority', '<i class=\"mdi mdi-shape-plus menu-icon\"></i>', '', '', ''),
(5, 'Freelancer', 'freelancer', '<i class=\"mdi mdi-worker menu-icon\"></i>', '', '', '');
-- --------------------------------------------------------
--
-- Structure de la table `w551je5v_pb_platforms`
--
DROP TABLE IF EXISTS `w551je5v_pb_platforms`;
CREATE TABLE IF NOT EXISTS `w551je5v_pb_platforms` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`image` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`color` varchar(7) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`id_category` int NOT NULL,
`icon` varchar(300) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`icon_blue_img` varchar(300) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`icon_black_img` varchar(300) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`icon_white_img` varchar(300) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`application` varchar(900) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`url` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_category` (`id_category`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Déchargement des données de la table `w551je5v_pb_platforms`
--
INSERT INTO `w551je5v_pb_platforms` (`id`, `name`, `image`, `color`, `description`, `id_category`, `icon`, `icon_blue_img`, `icon_black_img`, `icon_white_img`, `application`, `url`) VALUES
(3, 'Twitter', 'twitter_automation.jpg', '#1C9CEA', 'hello 1', 1, '<i class=\"mdi mdi-twitter\"></i>', '', '', '', 'Twitter', 'https://www.twitter.com'),
(4, 'Linkedin', 'linkedin_automation.jpg', '#0077B5', 'Helllo 2', 1, '<i class=\"mdi mdi-linkedin\"></i>', '', '', '', 'Linkedin', 'https://www.linkedin.com'),
(5, 'Leboncoin', 'leboncoin_automation.jpg', '#F76B14', 'Hello 3', 3, '', 'leboncoin_blue.png', 'leboncoin_black.png', 'leboncoin_white.png', 'Leboncoin', 'https://www.leboncoin.com'),
(15, 'Facebook', 'facebook_automation.jpg', '#1877F2', 'hello 4', 1, '<i class=\"mdi mdi-facebook-box\"></i>', '', '', '', 'Facebook_Orca,Facebook_Katana', 'https://www.facebook.com'),
(22, 'Instagram', 'instagram_automation.jpg', '#A42DB6', 'hello 5', 1, '<i class=\"mdi mdi-instagram\"></i>', '', '', '', 'Instagram', 'https://www.instagram.com'),
(27, 'Twitter', 'twitter_automation.jpg', '#1DA1F2', 'hello 6', 2, '<i class=\"mdi mdi-twitter\"></i>', '', '', '', 'Twitter', 'https://www.twitter.com');
-- --------------------------------------------------------
--
-- Structure de la table `w551je5v_pb_tasks`
--
DROP TABLE IF EXISTS `w551je5v_pb_tasks`;
CREATE TABLE IF NOT EXISTS `w551je5v_pb_tasks` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`introduction` text NOT NULL,
`description` text NOT NULL,
`id_platform` int NOT NULL,
`id_type_task` int NOT NULL,
`url_keywords` varchar(300) NOT NULL,
`url_keywords_description` text NOT NULL,
`minimum` varchar(300) NOT NULL,
`minimum_description` text NOT NULL,
`url_list` varchar(300) NOT NULL,
`url_list_description` text NOT NULL,
`url_usernames` varchar(300) NOT NULL,
`url_usernames_description` text NOT NULL,
`daily_limit` int DEFAULT NULL,
`hourly_limit` int DEFAULT NULL,
`enable` int DEFAULT NULL,
`smartphone_allowed` int DEFAULT NULL,
`computer_allowed` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_platform` (`id_platform`),
KEY `id_type_task` (`id_type_task`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `w551je5v_pb_tasks`
--
INSERT INTO `w551je5v_pb_tasks` (`id`, `name`, `introduction`, `description`, `id_platform`, `id_type_task`, `url_keywords`, `url_keywords_description`, `minimum`, `minimum_description`, `url_list`, `url_list_description`, `url_usernames`, `url_usernames_description`, `daily_limit`, `hourly_limit`, `enable`, `smartphone_allowed`, `computer_allowed`) VALUES
(3, 'Group Members', 'intro 1', 'description 1', 15, 2, '0', '', '0', '', '1', 'list desc', '0', '', 15, 10, 1, 1, 1),
(4, 'Followers of accounts', 'intro 2', 'description 2', 22, 2, '0', '', '0', '', '1', 'list desc', '0', '', 20, 10, 1, 1, 1),
(5, 'Influencers', 'intro 3', 'description 3', 27, 1, '1', 'other descr', '1', 'other desc', '1', 'list desc', '0', '', 20, 10, 1, 1, 1);
-- --------------------------------------------------------
--
-- Structure de la table `w551je5v_pb_type_tasks`
--
DROP TABLE IF EXISTS `w551je5v_pb_type_tasks`;
CREATE TABLE IF NOT EXISTS `w551je5v_pb_type_tasks` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Déchargement des données de la table `w551je5v_pb_type_tasks`
--
INSERT INTO `w551je5v_pb_type_tasks` (`id`, `name`, `description`) VALUES
(1, 'Message', ''),
(2, 'Voice Message', '<h3 class=\"card-title\" style=\"font-size:1.1em;\"> <i class=\"mdi mdi-format-align-left\"></i> Text or Voice? </h3>'),
(3, 'Scrape', ''),
(5, 'Publish Post', ''),
(6, 'Share Post', ''),
(7, 'Follow', ''),
(8, 'Unfollow', ''),
(9, 'Like', 'wxc'),
(12, 'Add Friends', 'This task is for adding friends');
--
-- Contraintes pour les tables déchargées
--
--
-- Contraintes pour la table `w551je5v_pb_platforms`
--
ALTER TABLE `w551je5v_pb_platforms`
ADD CONSTRAINT `W551je5v_pb_platforms_ibfk_1` FOREIGN KEY (`id_category`) REFERENCES `w551je5v_pb_categories` (`ID`);
--
-- Contraintes pour la table `w551je5v_pb_tasks`
--
ALTER TABLE `w551je5v_pb_tasks`
ADD CONSTRAINT `W551je5v_pb_tasks_ibfk_1` FOREIGN KEY (`id_type_task`) REFERENCES `w551je5v_pb_type_tasks` (`id`),
ADD CONSTRAINT `W551je5v_pb_tasks_ibfk_2` FOREIGN KEY (`id_platform`) REFERENCES `w551je5v_pb_platforms` (`id`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Tags： nameiddefaulttypenotutf8nulltasks
热门问题
- 如何添加虚拟方法
- 如何添加表示整数的擦边字符串？
- 如何添加要在Bokeh中使用的新font.ttf文件？
- 如何添加要显示的矩阵XY轴编号和XY轴
- 如何添加计数？
- 如何添加计数器函数？
- 如何添加计数器列来计算数据帧中另一列中的特定值？
- 如何添加计数器来跟踪while循环中的月份和年份？
- 如何添加计数并删除countplot的顶部和右侧脊椎？
- 如何添加计时器wx.应用程序更新窗口对象的主循环？
- 如何添加评论到帖子？PostDetailVew，Django 2.1.5
- 如何添加评论拉梅尔亚姆
- 如何添加诸如矩阵Python/Pandas之类的数据帧？
- 如何添加谷歌地点自动完成到Flask？
- 如何添加超时、python discord bot
- 如何添加超过1dp的检查
- 如何添加距离方法
- 如何添加跟随游戏的敌人精灵
- 如何添加路径以便python可以找到程序？
- 如何添加身份验证/安全性以使用happybase访问HBase？
热门文章
- Python覆盖写入文件
- 怎样创建一个 Python 列表？
- Python3 List append()方法使用
- 派森语言
- Python List pop()方法
- Python Django Web典型模块开发实战
- Python input() 函数
- Python3 列表(list) clear()方法
- Python游戏编程入门
- 如何创建一个空的set?
- python如何定义(创建)一个字符串
- Python标准库 [The Python Standard Library by Ex
- Python网络数据爬取及分析从入门到精通（分析篇）
- Python3 for 循环语句
- Python List insert() 方法
- Python 字典(Dictionary) update()方法
- Python编程无师自通 专业程序员的养成
- Python3 List count()方法
- Python 网络爬虫实战 [Web Crawler With Python]
- Python Cookbook（第2版）中文版
使用
mysql_connection.cursor(dictionary=True)连接数据库的方式返回的行为dict。dict的属性是它可以有唯一的键，在您的例子中，表name中的所有列都具有相同的名称。所以这个name只能有一个键要克服这个问题，您需要像这样给列名加上别名
SELECT tasks.id, tasks.name as tname, tasks.introduction, platforms.name as pname, type_tasks.name as ttname, categories.name as cname ...相关问题 更多 >
编程相关推荐