mysql连接python_Python MySQL-表联接

mysql连接python

In this tutorial, we will learn how to join two or more MySQL tables in Python.

在本教程中，我们将学习如何在Python中联接两个或多个MySQL表。

In order to combine two or more tables in MySQL, JOIN statement is used. One thing to note here is that there must be a common column in both tables based on which this operation will be performed.

为了在MySQL中合并两个或多个表 ，使用了JOIN语句。 这里要注意的一件事是， 两个表中都必须有一个公共列，将基于该列执行此操作。

We have already created the students table in the studytonight database(from the Python MySQL create table tutorial). Let us create another table named results after that we will join both the tables.

我们已经在studytonight数据库中创建了students表(来自Python MySQL create table教程)。 让我们创建另一个名为results的表，之后将两个表联接在一起。

Below we have code to create a table named results. Both results and students table have one field common that is rollno.

下面我们有代码来创建一个名为results的表。 结果表和学生表都有一个共同的字段，即rollno 。

Yuo can directly run the query to create the new table in MySQL command line.

Yuo可以直接在MySQL命令行中运行查询以创建新表。

Python MySQL-连接两个表 (Python MySQL - Joining two tables)

Below we have a code snippet where we will join two tables named results and students based on the column rollno:

下面是一个代码片段，其中我们将根据rollno列连接两个名为results和students的表 ：

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

sql = "SELECT students.rollno, students.name,students.Branch,students.Address, results.status from students INNER JOIN results ON students.rollno=results.rollno;"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
    print(x)

The output of the above code will be:

上面代码的输出将是：

(1, 'Ramesh', 'CSE', '149 Indirapuram', 'Pass') (2, 'Peter', 'ME', 'Noida', 'Fail') (3, 'Navin', 'CE', 'New Delhi', 'Pass')

(1，'Ramesh'，'CSE'，'149 Indirapuram'，'Pass')(2，'Peter'，'ME'，'Noida'，'Fail')(3，'Navin'，'CE'， “新德里”，“通行证”)

The above output indicates that both tables are joined.

上面的输出表明两个表都已连接。

Here is the snapshot of the actual output:

这是实际输出的快照：

Python MySQL-左联接 (Python MySQL - Left Join)

It is important to note that the INNER JOIN(which we covered in the example above) only shows the rows in the resultset when there is a match.

重要的是要注意， INNER JOIN (我们在上面的示例中进行了介绍)仅在存在match时显示结果集中的行。

If you want to fetch all the records fromthea left-hand side table even if there is no match then Left Join will be used.

如果您希望从左侧表中获取所有记录，即使没有匹配项，也将使用Left Join 。

Let us see an example given below for the Left Join:

让我们看一下下面给出的左连接示例：

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

sql = "SELECT students.rollno, students.name,students.Branch,students.Address, results.status from students LEFT JOIN results ON students.rollno=results.rollno;"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
    print(x)

The output of the above code is as follows. Let us see:

上面代码的输出如下。 让我们看看：

(1, 'Ramesh', 'CSE', '149 Indirapuram', 'Pass') (2, 'Peter', 'ME', 'Noida', 'Fail') (3, 'Navin', 'CE', 'New Delhi', 'Pass') (5, 'suraj', 'ECE', 'Panipat', None) (6, 'Mukesh', 'CSE', 'Samalkha', None)

(1，'Ramesh'，'CSE'，'149 Indirapuram'，'Pass')(2，'Peter'，'ME'，'Noida'，'Fail')(3，'Navin'，'CE'， '新德里'，'通行证')(5，'suraj'，'ECE'，'Panipat'，无)(6，'Mukesh'，'CSE'，'Samalkha'，无)

In the above output, the status of rollno 5 and 6 is None because their result is not mentioned in the results table. But as we have applied LEFT JOIN so the query selects all the rows from the left table even if there is no match.

在上面的输出中， rollno 5和6的状态为None，因为结果表中未提及它们的结果 。 但是，由于我们应用了LEFT JOIN因此即使没有匹配项 ，查询也会从左表中选择所有行。

Here is the snapshot of the actual output:

这是实际输出的快照：

Python MySQL-右连接 (Python MySQL - Right Join)

If you want to fetch all the records from the right-hand side table even if there is no match then Right Join will be used.

如果您希望从右侧表中获取所有记录，即使没有匹配项，也将使用“ Right Join 。

Let us see an example given below for the Right Join. The code given below will fetch all the rows from the right-hand side table. It will not return those rows with rollno 5 and 6:

让我们看一下下面给出的“ Right Join示例。 下面给出的代码将从右侧表中获取所有行。 它不会返回那些具有rollno 5和6的行：

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

sql = "SELECT students.rollno, students.name,students.Branch,students.Address, results.status from students RIGHT JOIN results ON students.rollno=results.rollno;"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
    print(x)

The output of the above code is as follows:

上面代码的输出如下：

(1, 'Ramesh', 'CSE', '149 Indirapuram', 'Pass') (2, 'Peter', 'ME', 'Noida', 'Fail') (3, 'Navin', 'CE', 'New Delhi', 'Pass')

(1，'Ramesh'，'CSE'，'149 Indirapuram'，'Pass')(2，'Peter'，'ME'，'Noida'，'Fail')(3，'Navin'，'CE'， “新德里”，“通行证”)

Here is the snapshot of the actual output:

这是实际输出的快照：

And with this we have covered all the basics of Python MySQL. If you have to develop an application in which you want to have a database with multipl tables, in which you want to store data and retrieve data from the tables, then we hope these tutorials help you.

至此，我们涵盖了Python MySQL的所有基础知识。 如果您必须开发一个应用程序，在该应用程序中要具有包含多表的数据库，并希望在其中存储数据并从表中检索数据，那么我们希望这些教程对您有所帮助。

翻译自: https://www.studytonight.com/python/python-mysql-table-join

mysql连接python