Python Mysql

安装 mysql.connector

要将 python 应用与 MySQL 数据库连接起来，我们必须在程序中导入 mysql.connector 模块。

mysql.connector 不是 python 安装附带的内置模块。我们需要安装它让它工作。

执行以下命令，使用 pip 安装程序安装它。

>  python -m pip install mysql-connector

或者按照以下步骤操作。

1.点击链接:

https://files . python hosted . org/packages/8f/6d/fb8 ebcbaee 68 b 172 C3 DFD 08 c7b 8660d 09 f 91 d8d 5411298 bcacbd 309 f 96/MySQL-connector-python-8 . 0 . 13 . tar . gz下载源代码。

2.提取存档文件。

3.打开终端(windows 的 CMD)，将当前工作目录改为源代码目录。

$  cd mysql-connector-python-8.0.13/

4.使用参数 build 运行名为 setup.py 的文件。

$ python setup.py build

5.运行以下命令来安装 mysql 连接器。

$ python setup.py install

为 python 安装 mysql 连接器需要一点时间。一旦这个过程结束，我们可以通过在 python shell 上导入 mysql 连接器来验证安装。

因此，我们已经成功地在系统上安装了 python 的 mysql 连接器。

数据库连接

将 python 应用连接到我们的数据库有以下步骤。

1. 导入 mysql.connector 模块

2. 创建连接对象。

3. 创建光标对象

4. 执行查询

---

创建连接

为了在 MySQL 数据库和 python 应用之间创建连接，使用了 mysql.connector 模块的 connect()方法。

在方法调用中传递数据库详细信息，如主机名、用户名和数据库密码。方法返回连接对象。

使用 connect()的语法如下。

Connection-Object= mysql.connector.connect(host = <host-name> , user = <username> , passwd = <password> )

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")

#printing the connection object 
print(myconn)

输出:

<mysql.connector.connection.MySQLConnection object at 0x7fb142edd780> 

这里，我们必须注意，如果我们想要连接到特定的数据库，我们可以在 connect()方法中指定数据库名称。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google", database = "mydb")

#printing the connection object 
print(myconn) 

输出:

<mysql.connector.connection.MySQLConnection object at 0x7ff64aa3d7b8> 

创建光标对象

光标对象可以被定义为 Python 数据库 API 2.0 中指定的抽象。它通过与数据库的相同连接，方便我们拥有多个独立的工作环境。我们可以通过调用连接对象的“cursor”函数来创建 cursor 对象。游标对象是对数据库执行查询的一个重要方面。

下面给出了创建光标对象的语法。

<my_cur>  = conn.cursor()

例子

import mysql.connector
#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google", database = "mydb")

#printing the connection object 
print(myconn) 

#creating the cursor object
cur = myconn.cursor()

print(cur)

输出:

<mysql.connector.connection.MySQLConnection object at 0x7faa17a15748> 
MySQLCursor: (Nothing executed yet)

创建新数据库

获取现有数据库的列表

我们可以通过使用下面的 MySQL 查询获得所有数据库的列表。

>  show databases;

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")

#creating the cursor object
cur = myconn.cursor()

try:
    dbs = cur.execute("show databases")
except:
    myconn.rollback()
for x in cur:
    print(x)
myconn.close()

输出:

('EmployeeDB',)
('Test',)
('TestDB',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mysql',)
('performance_schema',)
('testDB',)

创建新数据库

可以使用以下 SQL 查询创建新数据库。

>  create database <database-name>  

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")

#creating the cursor object
cur = myconn.cursor()

try:
    #creating a new database
    cur.execute("create database PythonDB2")

    #getting the list of all the databases which will now include the new database PythonDB
    dbs = cur.execute("show databases")

except:
    myconn.rollback()

for x in cur:
        print(x)

myconn.close()

输出:

('EmployeeDB',)
('PythonDB',)
('Test',)
('TestDB',)
('anshika',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mydb1',)
('mysql',)
('performance_schema',)
('testDB',)

创建表

在教程的这一部分中，我们将创建新的表 Employee。我们必须在建立连接对象时提到数据库名称。

我们可以使用 SQL 的 CREATE TABLE 语句来创建新表。在我们的数据库 PythonDB 中，Employee 表最初将有四列，即姓名、id、工资和 department_id。

以下查询用于创建新表“员工”。

>  create table Employee (name varchar(20) not null, id int primary key, salary float not null, Dept_Id int not null)

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Creating a table with name Employee having four columns i.e., name, id, salary, and department id
    dbs = cur.execute("create table Employee(name varchar(20) not null, id int(20) not null primary key, salary float not null, Dept_id int not null)")
except:
    myconn.rollback()

myconn.close()

现在，我们可以检查数据库中是否存在雇员表。

更改表

有时，我们可能会忘记创建一些列，或者我们可能需要更新表模式。如果需要，alter 语句用于更改表架构。在这里，我们将把列 branch_name 添加到表 Employee 中。以下 SQL 查询用于此目的。

alter table Employee add branch_name varchar(20) not null

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #adding a column branch name to the table Employee
    cur.execute("alter table Employee add branch_name varchar(20) not null")
except:
    myconn.rollback()

myconn.close()

插入操作

向表中添加记录

INSERT INTO 语句用于向表中添加一条记录。在 python 中，我们可以用格式说明符(%s)代替值。

我们在游标的 execute()方法中以元组的形式提供实际值。

考虑下面的例子。

例子

import mysql.connector
#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"

#The row values are provided in the form of tuple 
val = ("John", 110, 25000.00, 201, "Newyork")

try:
    #inserting the values into the table
    cur.execute(sql,val)

    #commit the transaction 
    myconn.commit()

except:
    myconn.rollback()

print(cur.rowcount,"record inserted!")
myconn.close()

输出:

1 record inserted!

---

插入多行

我们还可以使用 python 脚本一次插入多行。多行被称为各种元组的列表。

列表的每个元素被视为一个特定的行，而元组的每个元素被视为一个特定的列值(属性)。

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"
val = [("John", 102, 25000.00, 201, "Newyork"),("David",103,25000.00,202,"Port of spain"),("Nick",104,90000.00,201,"Newyork")]

try:
    #inserting the values into the table
    cur.executemany(sql,val)

    #commit the transaction 
    myconn.commit()
    print(cur.rowcount,"records inserted!")

except:
    myconn.rollback()

myconn.close()

输出:

3 records inserted! 

---

行标识

在 SQL 中，一个特定的行由一个插入 id 来表示，该 id 被称为行 id。我们可以通过使用 cursor 对象的属性 lastrowid 来获取最后插入的行 id。

考虑下面的例子。

例子

import mysql.connector
#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()

sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"

val = ("Mike",105,28000,202,"Guyana")

try:
    #inserting the values into the table
    cur.execute(sql,val)

    #commit the transaction 
    myconn.commit()

    #getting rowid
    print(cur.rowcount,"record inserted! id:",cur.lastrowid)

except:
    myconn.rollback()

myconn.close()

输出:

1 record inserted! Id: 0

读取操作

SELECT 语句用于从数据库中读取值。我们可以通过在 SQL 中使用各种子句来限制选择查询的输出，如 where、limit 等。

Python 提供的 fetchall()方法以行的形式返回存储在表中的数据。我们可以迭代结果来获得单个行。

在教程的这一部分，我们将使用 python 脚本从数据库中提取数据。我们还将格式化输出，将其打印在控制台上。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select * from Employee")

    #fetching the rows from the cursor object
    result = cur.fetchall()
    #printing the result

    for x in result:
        print(x);
except:
    myconn.rollback()

myconn.close()

输出:

('John', 101, 25000.0, 201, 'Newyork')
('John', 102, 25000.0, 201, 'Newyork')
('David', 103, 25000.0, 202, 'Port of spain')
('Nick', 104, 90000.0, 201, 'Newyork')
('Mike', 105, 28000.0, 202, 'Guyana')

---

读取特定列

我们可以通过提及特定列的名称来阅读它们，而不是使用星号(*)。

在下面的示例中，我们将从 Employee 表中读取姓名、id 和工资，并将其打印在控制台上。

例子

import mysql.connector
#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee")

    #fetching the rows from the cursor object
    result = cur.fetchall()
    #printing the result
    for x in result:
        print(x);
except:
    myconn.rollback()
myconn.close()

输出:

('John', 101, 25000.0)
('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)

---

fetchone()方法

fetchone()方法仅用于从表中获取一行。fetchone()方法返回结果集的下一行。

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee")

    #fetching the first row from the cursor object
    result = cur.fetchone()

    #printing the result
    print(result)

except:
    myconn.rollback()

myconn.close()

输出:

('John', 101, 25000.0)

---

格式化结果

我们可以通过迭代游标对象的 fetchall()或 fetchone()方法产生的结果来格式化结果，因为结果作为不可读的元组对象存在。

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:

    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");
    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出:

Name    id    Salary
John    101    25000
John    102    25000
David    103    25000
Nick    104    90000
Mike    105    28000

---

使用 where 子句

我们可以使用 where 子句来限制 select 语句产生的结果。这将只提取那些满足 where 条件的列。

考虑下面的例子。

示例:打印以 j 开头的名称

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee where name like 'J%'")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");

    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出:

Name    id    Salary
John    101    25000
John    102    25000

示例:打印 id = 101、102 和 103 的名称

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee where id in (101,102,103)")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");

    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出:

Name    id    Salary
John    101    25000
John    102    25000
David    103    2500

---

排序结果

ORDER BY 子句用于排序结果。考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee order by name")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");

    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出:

Name    id    Salary
David   103    25000
John    101    25000
John    102    25000
Mike    105    28000
Nick    104    90000

---

DESC 的命令

这将按特定列的降序排序结果。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee order by name desc")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    #printing the result
    print("Name    id    Salary");
    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))

except:
    myconn.rollback()

myconn.close()

输出:

Name    id    Salary
Nick    104    90000
Mike    105    28000
John    101    25000
John    102    25000
David    103    25000

更新操作

UPDATE-SET 语句用于更新表中的任何列。以下 SQL 查询用于更新列。

>  update Employee set name = 'alex' where id = 110

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #updating the name of the employee whose id is 110
    cur.execute("update Employee set name = 'alex' where id = 110")
    myconn.commit()
except:

    myconn.rollback()

myconn.close()

---

删除操作

DELETE FROM 语句用于从表中删除特定记录。这里，我们必须使用 WHERE 子句强加一个条件，否则表中的所有记录都将被删除。

以下 SQL 查询用于从表中删除 id 为 110 的员工详细信息。

>  delete from Employee where id = 110

考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Deleting the employee details whose id is 110
    cur.execute("delete from Employee where id = 110")
    myconn.commit()
except:

    myconn.rollback()

myconn.close()

连接操作

我们可以通过使用 join 语句在两个或多个表中使用一些公共列来组合它们。

我们的数据库中只有一个表，让我们用两列 department _ id 和 department_name 再创建一个 Departments 表。

create table Departments (Dept_id int(20) primary key not null, Dept_Name varchar(20) not null);

因为我们已经创建了一个新的表 Departments，如上图所示。然而，我们还没有在其中插入任何值。

让我们插入一些部门 id 和部门名称，以便将其映射到我们的员工表。

insert into Departments values (201, "CS");
insert into Departments values (202, "IT");

让我们看看每个表中插入的值。请看下图。

现在，让我们创建一个 python 脚本，将公共列(即 dept_id)上的两个表连接起来。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #joining the two tables on departments_id
    cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments join Employee on Departments.Dept_id = Employee.Dept_id")
    print("ID    Name    Salary    Dept_Id    Dept_Name")
    for row in cur:
        print("%d    %s    %d    %d    %s"%(row[0], row[1],row[2],row[3],row[4]))

except:
    myconn.rollback()

myconn.close()

输出:

ID    Name    Salary    Dept_Id    Dept_Name
101   John    25000    	201    		CS
102   John    25000    	201    		CS
103   David   25000       202    		IT
104   Nick    90000   	201    		CS
105   Mike    28000   	202   		IT

---

右连接

右连接显示右侧表的所有列，因为我们在数据库 PythonDB 中有两个表，即 Departments 和 Employee。表中没有任何员工不在任何部门工作(部门标识为空的员工)。然而，为了理解右连接的概念，让我们创建一个。

在 MySQL 服务器上执行以下查询。

insert into Employee(name, id, salary, branch_name) values ("Alex",108,29900,"Mumbai"); 

这将插入一个不在任何部门工作的员工 Alex(部门 id 为空)。

现在，我们在“员工”表中有一名员工，其部门 id 不在“部门”表中。现在让我们在两个表上执行正确的连接。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #joining the two tables on departments_id
    result = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments right join Employee on Departments.Dept_id = Employee.Dept_id")

	print("ID    Name    Salary    Dept_Id    Dept_Name")

	for row in cur:
        print(row[0],"    ", row[1],"    ",row[2],"    ",row[3],"    ",row[4])

except:
    myconn.rollback()

myconn.close()

输出:

ID    Name    Salary    Dept_Id    Dept_Name
101      John      25000.0      201      CS
102      John      25000.0      201      CS
103      David      25000.0      202      IT
104      Nick      90000.0      201      CS
105      Mike      28000.0      202      IT
108      Alex      29900.0      None      None

---

左连接

左连接包含左侧表中的所有数据。它对右连接的效果正好相反。考虑下面的例子。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #joining the two tables on departments_id
    result = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments left join Employee on Departments.Dept_id = Employee.Dept_id")
    print("ID    Name    Salary    Dept_Id    Dept_Name")
    for row in cur:
        print(row[0],"    ", row[1],"    ",row[2],"    ",row[3],"    ",row[4])

except:
    myconn.rollback()

myconn.close()

输出:

ID    Name    Salary    Dept_Id    Dept_Name
101      John      25000.0      201      CS
102      John      25000.0      201      CS
103      David      25000.0      202      IT
104      Nick      90000.0      201      CS
105      Mike      28000.0      202      IT

执行事务

事务确保数据库的数据一致性。我们必须确保在执行数据库操作时，不能有多个应用修改记录。这些事务具有以下属性。

1. 原子性
   要么事务完成，要么什么都没发生。如果一个事务包含 4 个查询，那么所有这些查询都必须执行，或者都不执行。

2. 一致性
   事务开始前数据库必须一致，事务完成后数据库也必须一致。

3. 隔离
   事务的中间结果在当前事务之外不可见。

4. 持久性
   一旦事务被提交，即使在系统故障后，其影响也是持久的。

---

Python commit()方法

Python 提供了 commit()方法，确保对

数据库不断地发生变化。

下面给出了使用 commit()方法的语法。

conn.commit() #conn is the connection object 

在调用 commit()之前，修改数据库记录的所有操作都不会发生。

---

Python rollback()方法

rollback()方法用于恢复对数据库所做的更改。这种方法很有用，因为如果在数据库操作过程中发生了一些错误，我们可以回滚该事务以保持数据库的一致性。

使用 rollback()的语法如下。

Conn.rollback()

---

关闭连接

一旦我们完成了所有关于数据库的操作，我们就需要关闭数据库连接。Python 提供了 close()方法。下面给出了使用 close()方法的语法。

conn.close() 

在下面的示例中，我们删除了所有在 CS 部门工作的员工。

例子

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    cur.execute("delete from Employee where Dept_id = 201")
    myconn.commit()
    print("Deleted !")
except:
    print("Can't delete !")
    myconn.rollback()

myconn.close()

输出:

Deleted !