Three articles to help you figure out how to learn mysql database and create tables in the mysql library

Previously we had a simple understanding of Python's manipulation of mysql database through "Three articles to help you learn how to learn mysql database and install SQL database" This article will introduce it Mysql library creates table , and learns in depth mysql database.

First of all, if you want to create a table for the mysql library, you must first connect to the database

Before connecting to the database , please confirm the following first:

1. You have created the database TESTDB.

2. In the TESTDB database you have created the table EMPLOYEE

3. The EMPLOYEE table The fields are FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.

4. The user name used to connect to the database TESTDB is "testuser" and the password is "test123". You can set it yourself or directly use the root username and password. For Mysql database user authorization, please use the Grant command. .

5. The Python MySQLdb module has been installed on your machine.

6. If you are not familiar with sql statements, you can visit our SQL basic tutorial

Example:

The following example links to Mysql's TESTDB database:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标
 cursor = db.cursor()
 
 # 使用execute方法执行SQL语句
 cursor.execute("SELECT VERSION()")
 
 # 使用 fetchone() 方法获取一条数据
 data = cursor.fetchone()
 
 print "Database version : %s " % data
 
 # 关闭数据库连接
 db.close()

The output result of executing the above script is as follows:

Database version : 5.0.45

Create database table

If the database connection exists, we can use the execute() method to Create a table in the database as follows: EMPLOYEE

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
         
cursor.execute(sql)

#关闭数据库连接d
b.close()

The above is the detailed content of Three articles to help you figure out how to learn mysql database and create tables in the mysql library. For more information, please follow other related articles on the PHP Chinese website!