Python Data Access 简明教程

Python MySQL - Create Table

CREATE TABLE 语句用于在 MySQL 数据库中创建表。在这里，你需要指定表的名称以及每个列的定义（名称和数据类型）。

The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.

Syntax

下面是创建 MySQL 表的语法：

Following is the syntax to create a table in MySQL −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Example

以下查询在 MySQL 中创建一个名为 EMPLOYEE 的表，该表具有五个列，即 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。

Following query creates a table named EMPLOYEE in MySQL with five columns namely, FIRST_NAME, LAST_NAME, AGE, SEX and, INCOME.

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);
Query OK, 0 rows affected (0.42 sec)

DESC 语句提供指定表的描述。使用此语句可以验证是否已创建表，如下所示：

The DESC statement gives you the description of the specified table. Using this you can verify if the table has been created or not as shown below −

mysql> Desc Employee;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| FIRST_NAME | char(20) | NO   |     | NULL    |       |
| LAST_NAME  | char(20) | YES  |     | NULL    |       |
| AGE        | int(11)  | YES  |     | NULL    |       |
| SEX        | char(1)  | YES  |     | NULL    |       |
| INCOME     | float    | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.07 sec)

Creating a table in MySQL using python

名为 execute() （在游标对象上调用）的方法接受两个变量：

The method named execute() (invoked on the cursor object) accepts two variables −

A String value representing the query to be executed.
An optional args parameter which can be a tuple or, list or, dictionary, representing the parameters of the query (values of the place holders).

它返回一个整数值，表示受查询影响的行数。

It returns an integer value representing the number of rows effected by the query.

一旦建立数据库连接，就可以通过将 CREATE TABLE 查询传递给 execute() 方法来创建表。

Once a database connection is established, you can create tables by passing the CREATE TABLE query to the execute() method.

简而言之，要使用 python 7minus 创建表；

In short, to create a table using python 7minus;

Import mysql.connector package.
Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
Create a cursor object by invoking the cursor() method on the connection object created above.
Then, execute the CREATE TABLE statement by passing it as a parameter to the execute() method.

Example

以下示例在 mydb 数据库中创建了一个名为 Employee 的表。

Following example creates a table named Employee in the database mydb.

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Dropping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)

#Closing the connection
conn.close()