Python MySQL 查询多个join

Question

我想将几个表相互连接起来。数据库连接已建立，但出现此错误:

SyntaxError: EOL while scanning string literal

import mysql.connector
from mysql.connector import Error
import csv
import pandas as pd
import sqlite3
import numpy as np

cnx = mysql.connector.connect(user = "n.neshat",
                                         passwd = "Start123", 
                                         db = "base",
                                         host="192.168.126.29")
cursor = cnx.cursor()

test = ("select * from projects\ 
        join positions\ on projects.id  = positions.project_id\
        join loads\ on positions.id = loads.position_id\
        join equipments\ on positions.id = equipments.position_id\
        join module_fields\ on positions.id = module_fields.position_id\
        join configurations\ on positions.id = configurations.position_id\
        join module_blocks\ on configurations.id = module_blocks.configuration_id")

cursor.execute(test)

myresult = cursor.fetchall()

Answer 1

为避免意外的 SQL 语法拼写错误，我建议使用 Python 的多行语法。而且，使用别名:

test = """SELECT * FROM projects pr
          INNER JOIN positions po ON pr.id  = po.project_id
          INNER JOIN loads l ON po.id = l.position_id
          INNER JOIN equipments e ON po.id = e.position_id
          INNER JOIN module_fields mf ON po.id = mf.position_id
          INNER JOIN configurations c ON po.id = c.position_id
          INNER JOIN module_blocks mb ON c.id = mb.configuration_id"""