Python MySQL入门详细教程

本文概述

• 连接和创建
• 创建数据库
• 创建表
• 创建主键
• 插入数据
• 查询数据
• Where子句
• 更新数据
• 总结

先决条件

• Python数据类型, 控制结构, 循环等, 如果你是Python新手, 请学习Datacamp的免费的Python数据科学入门课程。
• SQL基础知识。如果你不懂SQL, 请参加Datacamp的免费SQL课程。

注意：

• 本教程并不旨在教你完整的SQL语法或语言。
• 本教程将教你如何在Python中使用MySQL。

在Python中遵循的与MySQL配合使用的过程

1. 连接到数据库。
2. 为你的数据库创建一个对象。
3. 执行SQL查询。
4. 从结果中获取记录。
5. 如果你在表中进行了任何更改, 请通知数据库。

1.安装MySQL

MySQL是最受欢迎的数据库之一。

从MySQL的官方网站下载并安装MySQL。你需要安装MySQL服务器才能遵循本教程。

接下来, 你必须为Python安装mysql.connector。我们需要mysql.connector将Python脚本连接到MySQL数据库。从这里下载mysql.connector并将其安装在你的计算机上。

现在, 使用以下代码检查你是否正确安装了mysql.connector。

import mysql.connector

如果上面的代码运行没有任何错误, 则说明你已经成功安装了mysql.connector, 可以使用了。

连接和创建

现在, 我们将使用MySQL的用户名和密码连接到数据库。如果你忘记了用户名或密码, 请使用密码创建一个新用户。

要创建新用户, 请参考MySQL官方文档。

现在, 使用你的用户名和密码连接到数据库。

## Connecting to the database

## importing 'mysql.connector' as mysql for convenient
import mysql.connector as mysql

## connecting to the database using 'connect()' method
## it takes 3 required parameters 'host', 'user', 'passwd'
db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms"
)

print(db) # it will print a connection object if everything is fine

<mysql.connector.connection_cext.CMySQLConnection object at 0x0000020C26A84C50>

就是这样, 现在你已连接到MySQL数据库。

创建数据库

现在, 我们将创建一个名为datacamp的数据库。

要在MySQL中创建数据库, 我们使用CREATE DATABASE database_name语句。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms"
)

## creating an instance of 'cursor' class which is used to execute the 'SQL' statements in 'Python'
cursor = db.cursor()

## creating a databse called 'datacamp'
## 'execute()' method is used to compile a 'SQL' statement
## below statement is used to create tha 'datacamp' database
cursor.execute("CREATE DATABASE datacamp")

如果数据库已经存在, 你将得到一个错误。确保该数据库不存在。

使用以下代码查看MySQL中存在的所有数据库。

要查看所有数据库, 我们使用SHOW DATABASES语句。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms"
)

cursor = db.cursor()

## executing the statement using 'execute()' method
cursor.execute("SHOW DATABASES")

## 'fetchall()' method fetches all the rows from the last executed statement
databases = cursor.fetchall() ## it returns a list of all databases present

## printing the list of databases
print(databases)

## showing one by one database
for database in databases:
    print(database)

[('datacamp', ), ('information_schema', ), ('mysql', ), ('performance_schema', ), ('sakila', ), ('sys', ), ('world', )]
('datacamp', )
('information_schema', )
('mysql', )
('performance_schema', )
('sakila', )
('sys', )
('world', )

创建表

在数据库中创建表以存储信息。在创建表之前, 我们必须首先选择一个数据库。

运行以下代码, 以选择我们在一分钟前创建的datacamp数据库。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

如果数据库存在, 则上面的代码将正确执行。现在, 你已连接到名为datacamp的数据库。

使用CREATE TABLE table_name在所选数据库中创建一个表。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## creating a table called 'users' in the 'datacamp' database
cursor.execute("CREATE TABLE users (name VARCHAR(255), user_name VARCHAR(255))")

你已经在datacamp数据库中成功创建了表用户。使用SHOW TABLES语句查看数据库中存在的所有表。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

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)

('users', )

创建主键

主键：-它是表中的唯一值。这有助于在表中唯一地找到每一行。

要创建主键, 我们在创建表时使用PRIMARY KEY语句。

INT INT AUTO_INCREMENT PRIMARY KEY语句用于以每行从1开始的唯一标识。

让我们看看如何为表创建主键。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## first we have to 'drop' the table which has already created to create it again with the 'PRIMARY KEY'
## 'DROP TABLE table_name' statement will drop the table from a database
cursor.execute("DROP TABLE users")

## creating the 'users' table again with the 'PRIMARY KEY'
cursor.execute("CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), user_name VARCHAR(255))")

要查看该表, 请运行以下代码。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## 'DESC table_name' is used to get all columns information
cursor.execute("DESC users")

## it will print all the columns as 'tuples' in a list
print(cursor.fetchall())

[('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment'), ('name', 'varchar(255)', 'YES', '', None, ''), ('user_name', 'varchar(255)', 'YES', '', None, '')]

删除主键

我们使用ALTER TABLE table_name DROP column_name语句删除具有主键的列。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## dropping the 'id' column
cursor.execute("ALTER TABLE users DROP id")

cursor.execute("DESC users")

print(cursor.fetchall())

[('name', 'varchar(255)', 'YES', '', None, ''), ('user_name', 'varchar(255)', 'YES', '', None, '')]

现在, 我们删除了“主键”列。让我们看看如何将带有主键的列添加到现有表中。

添加主键

将主键添加到现有表。我们使用ALTER TABLE table_name ADD PRIMARY KEY(column_name)语句向表添加主键。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## adding 'id' column to the 'users' table
## 'FIRST' keyword in the statement will add a column in the starting of the table
cursor.execute("ALTER TABLE users ADD COLUMN id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST")

cursor.execute("DESC users")

print(cursor.fetchall())

[('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment'), ('name', 'varchar(255)', 'YES', '', None, ''), ('user_name', 'varchar(255)', 'YES', '', None, '')]

我们已将列ID添加到users表。

插入数据

将数据插入表以进行存储。使用INSERT INTO table_name(column_names)VALUES(数据)语句插入表中。

插入单行

让我们看看如何在表格中插入一行。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "INSERT INTO users (name, user_name) VALUES (%s, %s)"
## storing values in a variable
values = ("Hafeez", "hafeez")

## executing the query with values
cursor.execute(query, values)

## to make final output we have to run the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "record inserted")

1 record inserted

上面的代码将在用户表中插入一行。

插入多行

让我们看看如何在表中插入多行。

要将多行插入到表中, 我们使用executemany()方法。它使用一个元组列表, 其中包含数据作为第二个参数和一个查询作为第一个参数。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "INSERT INTO users (name, user_name) VALUES (%s, %s)"
## storing values in a variable
values = [
    ("Peter", "peter"), ("Amy", "amy"), ("Michael", "michael"), ("Hennah", "hennah")
]

## executing the query with values
cursor.executemany(query, values)

## to make final output we have to run the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "records inserted")

4 records inserted

上面的代码在用户表中插入了四个记录。

查询数据

要从表中检索数据, 请使用SELECT column_names FROM table_name语句。

从表中获取所有记录

要从表中获取所有记录, 我们使用*代替列名。让我们从之前插入的users表中获取所有数据。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

(1, 'Hafeez', 'hafeez')
(2, 'Peter', 'peter')
(3, 'Amy', 'amy')
(4, 'Michael', 'michael')
(5, 'Hennah', 'hennah')

获取一些专栏

要从表中选择某些列, 请在语句中的SELECT之后提及列名称。让我们从用户表中检索用户名列。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT user_name FROM users"

## getting 'user_name' column from the table
cursor.execute(query)

## fetching all usernames from the 'cursor' object
usernames = cursor.fetchall()

## Showing the data
for username in usernames:
    print(username)

('hafeez', )
('peter', )
('amy', )
('michael', )
('hennah', )

你还可以一次检索多个列, 如下所示。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT name, user_name FROM users"

## getting 'name', 'user_name' columns from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
data = cursor.fetchall()

## Showing the data
for pair in data:
    print(pair)

('Hafeez', 'hafeez')
('Peter', 'peter')
('Amy', 'amy')
('Michael', 'michael')
('Hennah', 'hennah')

Where子句

WHERE用于在某些条件下选择数据。现在, 我们将选择一个ID为5的记录。

SELECT column_name FROM table_name WHERE条件语句将用于在某些条件下检索数据。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users WHERE id = 5"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

(5, 'Hennah', 'hennah')

你可以根据数据指定任何条件。

6.订购依据

使用ORDER BY对结果进行升序或降序排序。默认情况下, 它以升序对结果进行排序, 使用关键字DESC以降序对结果进行排序。

SELECT column_names FROM table_name ORDER BY column_name语句将用于按列升序对结果进行排序。

SELECT column_names FROM table_name ORDER BY column_name DESC语句将用于按列的降序对结果进行排序。

使用名称列按升序对数据进行排序。让我们看一下代码。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users ORDER BY name"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

(3, 'Amy', 'amy')
(1, 'Hafeez', 'hafeez')
(5, 'Hennah', 'hennah')
(4, 'Michael', 'michael')
(2, 'Peter', 'peter')

按名称列按降序对数据进行排序。让我们看一下代码。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users ORDER BY name DESC"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

(2, 'Peter', 'peter')
(4, 'Michael', 'michael')
(5, 'Hennah', 'hennah')
(1, 'Hafeez', 'hafeez')
(3, 'Amy', 'amy')

7.删除

DELETE关键字用于从表中删除记录。

DELETE FROM table_name WHERE条件语句用于删除记录。如果你不指定条件, 那么所有记录将被删除。

让我们从用户表中删除ID为5的记录。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "DELETE FROM users WHERE id = 5"

## executing the query
cursor.execute(query)

## final step to tell the database that we have changed the table data
db.commit()

通过查询表中的所有记录来检查是否将其删除。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

(1, 'Hafeez', 'hafeez')
(2, 'Peter', 'peter')
(3, 'Amy', 'amy')
(4, 'Michael', 'michael')

第五条记录被删除。

更新数据

UPDATE关键字用于更新一个或多个记录的数据。

UPDATE table_name SET column_name = new_value WHERE条件语句用于更新特定行的值。

让我们将第一条记录的名称从Hafeez更新为Kareem。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "UPDATE users SET name = 'Kareem' WHERE id = 1"

## executing the query
cursor.execute(query)

## final step to tell the database that we have changed the table data
db.commit()

通过从数据中检索所有记录来检查数据是否已更新。

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

(1, 'Kareem', 'hafeez')
(2, 'Peter', 'peter')
(3, 'Amy', 'amy')
(4, 'Michael', 'michael')

请参阅, 第一个记录的名称已更改。

我没有讨论数据库对象的所有方法。你可以使用dir()方法检查所有方法。

总结

恭喜你！现在, 你可以使用Python使用MySQL数据库。

如果你对本教程有任何疑问, 请在评论部分中提及它们, 我将为你提供帮助。

在MySQLs的文档中了解有关MySQL的更多信息。

从Python的官方文档中学习Python