本文概述
- 连接和创建
- 创建数据库
- 创建表
- 创建主键
- 插入数据
- 查询数据
- Where子句
- 更新数据
- 总结
- Python数据类型, 控制结构, 循环等, 如果你是Python新手, 请学习Datacamp的免费的Python数据科学入门课程。
- SQL基础知识。如果你不懂SQL, 请参加Datacamp的免费SQL课程。
- 本教程并不旨在教你完整的SQL语法或语言。
- 本教程将教你如何在Python中使用MySQL。
- 连接到数据库。
- 为你的数据库创建一个对象。
- 执行SQL查询。
- 从结果中获取记录。
- 如果你在表中进行了任何更改, 请通知数据库。
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 mysqldb = 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 mysqldb = 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 mysqldb = mysql.connect(
host = "localhost", user = "root", passwd = "dbms", database = "datacamp"
)
如果数据库存在, 则上面的代码将正确执行。现在, 你已连接到名为datacamp的数据库。
使用CREATE TABLE table_name在所选数据库中创建一个表。
import mysql.connector as mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 mysqldb = 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 MySQL入门详细教程】从Python的官方文档中学习Python
推荐阅读
- 25行Python代码实现人脸识别
- 易水之畔 自动刷冒险任务 刷金币 安卓模拟器 手机助手
- Android中的runOnUiThread
- rg.apache.ibatis.binding.BindingException: Mapper method 'com.dao.Cameao.getOnlineDayRation atte
- create-react-app重建
- 严重: Error loading WebappClassLoader context:异常的解决方法(转)
- dubbo 解决既是消费者又是提供者 Duplicate application configs 的问题
- [RK3288][Android6.0] 关于uboot中logo相关知识点小结
- windows下安装Virtualenvwrapper