Python MySQL-创建并列出表

最新推荐文章于 2024-03-10 22:26:48 发布

cunfen6312

最新推荐文章于 2024-03-10 22:26:48 发布

阅读量427

点赞数

文章标签：数据库 mysql python java linux

原文链接：https://www.studytonight.com/python/python-mysql-create-table

版权

Now we will learn how to create tables in any MySQL database in Python and we will also see how to check if a table already exists or not by listing down all the tables in a database using Python.

现在，我们将学习如何在Python 中的任何MySQL数据库中创建表，并且还将看到如何通过使用Python列出数据库中的所有表来检查表是否已存在 。

Python MySQL-建立表格 (Python MySQL - Create Table)

Basically, to store information in the MySQL database there is a need to create the tables. It is also required to select our database first and then create tables inside that database.

基本上，要将信息存储在MySQL数据库中 ，需要创建表。 还需要先选择我们的数据库 ，然后在该数据库中创建表。

At the time of creating the connection, you can also specify the name of your database, like given below:

创建连接时，还可以指定数据库名称，如下所示：

import mysql.connector

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

If the above code is executed without any errors then it means you have successfully connected to the database named studytonight.

如果执行以上代码没有任何错误，则表明您已成功连接到名为studytonight的数据库。

SQL查询创建表 (SQL Query to Create Table)

To create a table in the selected database the following statement will be used. Let us see the syntax:

要在所选数据库中创建表，将使用以下语句。让我们看一下语法：

CREATE TABLE table_name;

Let us create a table named students in the specified database, that is studytonight

让我们在指定的数据库中创建一个名为学生的表，即studytonight

In the table students we will have the following fields: name, rollno, branch, and address.

在学生表中，我们将具有以下字段： name ， rollno ， branch和address 。

#for our convenience we will import mysql.connector as mysql
import mysql.connector as mysql

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

cursor = db.cursor()

cursor.execute("CREATE TABLE students (name VARCHAR(255), rollno INTEGER(100), branch VARCHAR(255), address VARCHAR(255))")

If this code executes without any error then it means the table has been created successfully.

如果执行此代码没有任何错误，则表明该表已成功创建。

Now, If you want to check the existing tables in the database then you can use the SHOW TABLES SQL statement.

现在，如果要检查数据库中的现有表 ，则可以使用SHOW TABLES SQL语句。

列出数据库中的现有表 (List existing Tables in a Database)

Now as we have created a table in our database. Let us check the tables that exist in our database. Use the code given below:

现在，我们已经在数据库中创建了一个表。让我们检查数据库中存在的表。使用下面给出的代码：

#for our convenience we will import mysql.connector as mysql
import mysql.connector as mysql

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

cursor = db.cursor()
## getting all the tables which are present in 'datacamp' database
cursor.execute("SHOW TABLES")

tables = cursor.fetchall() ## it returns list of tables present in the database

## showing all the tables one by one
for table in tables:
    print(table)

The output of the above code is as follows

上面代码的输出如下

('students',)

('学生们'，)

Python MySQL-具有主键的表 (Python MySQL - Table with Primary Key)

As we had created a table named students in our database, in which we will store student data and fetch it whenever required. But while fetching data, we might find students with same name and that can lead to wrong data getting fetched, or cause some confusion.

当我们在数据库中创建了一个名为“ students”的表时，我们将在其中存储学生数据并在需要时获取它。但是在获取数据时，我们可能会发现同名学生 ，这可能会导致获取错误数据或引起一些混乱。

So to uniquely identify each record in a table we can use Primary Key in our tables. Let us see first what is a Primary key?

因此，要唯一标识表中的每个记录 ，我们可以在表中使用主键。首先让我们看看什么是主键？

什么是主键？ (What is Primary Key?)

A primary key is an attribute to make a column or a set of columns accept only unique values. With the help of the primary key, one can find each row uniquely in the table.

主键是使一列或一组列仅接受唯一值的属性 。借助主键，可以在表中唯一地找到每一行。

Watch this video to learn about DBMS Keys - DBMS Keys Explained with Examples

观看此视频以了解DBMS密钥- 示例中介绍的DBMS密钥

Thus in order to identify each row uniquely with a number starting from 1. We will use the syntax as follows:

因此，为了唯一地标识从1开始的每一行。我们将使用以下语法：

INT AUTO_INCREMENT PRIMARY KEY

Using the above code with any column, we can make its value as auto increment, which means the database will automatically add an incremented value even if you do not insert any value for that column while inserting a new row of data to your table.

将以上代码用于任何列，我们可以将其值设置为自动增量，这意味着即使您在向表中插入新数据行时未为该列插入任何值，数据库也会自动添加增量值。

在创建表期间添加主键 (Add Primary Key during Table creation)

Let us see how to add a primary key at the time of table creation. The code snippet for the same is given below:

让我们看看如何在创建表时添加主键 。相同的代码段如下所示：

import mysql.connector as mysql

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

## creating the 'students' table with the 'PRIMARY KEY'

cursor.execute("CREATE TABLE students (name VARCHAR(255), rollno INTEGER(100) NOT NULL AUTO_INCREMENT PRIMARY KEY, branch VARCHAR(255), address VARCHAR(255))")

If the above code runs without an error then it means you have successfully created a table named "students" with the column rollno as primary key.

如果上面的代码运行没有错误，则表明您已经成功创建了一个名为“ students”的表，其中rollno列为主键。

Python MySQL-描述表格 (Python MySQL - Describe the Table)

We can use the below python code to describe any table to see what all columns it has and all the meta information about the table and all its columns.

我们可以使用下面的python代码描述任何表，以查看表具有的所有列以及有关该表及其所有列的所有元信息。

import mysql.connector as mysql

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

cursor = db.cursor()

cursor.execute("DESC students")

print(cursor.fetchall())

The output will be:

输出将是：

[('name', 'varchar(255)', 'YES', '', None, ''), ('rollno', 'int', 'NO', 'PRI', None, 'auto_increment'), ('branch', 'varchar(255)', 'YES', '', None, ''), ('address', 'varchar(255)', 'YES', '', None, '')]

[('name'，'varchar(255)'，'YES'，''，None，'')，('rollno'，'int'，'NO'，'PRI'，None，'auto_increment')， ('branch'，'varchar(255)'，'YES'，''，None，'')，('address'，'varchar(255)'，'YES'，''，None，'')]

将主键添加到现有表 (Add Primary Key to Existing Table)

Now in this example, we will assume that rollno column does not exist in our student table. So we will learn how to add a column to be used as primary key in an existing table.

现在，在此示例中，我们将假设在学生表中不存在rollno列。因此，我们将学习如何在现有表中添加 用作主键的列。

Let us see how to create a primary key on an existing table. The code snippet for the same is given below:

让我们看看如何在现有表上创建主键。相同的代码段如下所示：

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "[email protected]",
    database = "studytonight"
)

cursor = db.cursor()

## We are going to add rollno field with primary key in table students 

cursor.execute("ALTER TABLE students ADD COLUMN rollno INT AUTO_INCREMENT PRIMARY KEY")

print(cursor.fetchall())

In the above code we have used the SQL Alter Query to add a new column to an existing table. You can use the describe table query to see the new added column.

在上面的代码中，我们使用了SQL Alter Query将新列添加到现有表中。您可以使用describe table查询来查看新添加的列。

[('name', 'varchar(255)', 'YES', '', None, ''), ('branch', 'varchar(255)', 'YES', '', None, ''), ('address', 'varchar(255)', 'YES', '', None, ''), ('rollno', 'int', 'NO', 'PRI', None, 'auto_increment')]

[(''name'，'varchar(255)'，'YES'，''，None，``)，('branch'，'varchar(255)'，'YES'，''，None，'') ，('address'，'varchar(255)'，'YES'，''，None，'')，('rollno'，'int'，'NO'，'PRI'，None，'auto_increment')]]

So in this tutorial we have covered everything related to creating a new MySQL table in python. We covered how to alter a table, how to list down all the tables in a database, and how to define a column as a primary key.

因此，在本教程中，我们涵盖了与在python中创建新MySQL表相关的所有内容。我们介绍了如何更改表，如何列出数据库中的所有表以及如何将列定义为主键。