Coursera 笔记

Module 04 · Accessing Databases using Python

SQL Cheat Sheet: Accessing Databases using PythonSQL 速查表：使用 Python 访问数据库

SQLite

Topic 主题	Syntax 语法	Description 描述	Example 示例
connect()	`sqlite3.connect()`	Create a new database and open a database connection to allow sqlite3 to work with it. Call `sqlite3.connect()` to create a connection to the database INSTRUCTOR.db in the current working directory, implicitly creating it if it does not exist. 创建一个新的数据库并打开一个数据库连接，以允许 sqlite3 与其进行交互。调用 `sqlite3.connect()` 以在当前工作目录中创建与数据库 INSTRUCTOR.db 的连接，如果它不存在，则隐式创建。	`import sqlite3` `con = sqlite3.connect("INSTRUCTOR.db")`
cursor()	`con.cursor()`	To execute SQL statements and fetch results from SQL queries, use a database cursor. Call `con.cursor()` to create the Cursor. 要执行 SQL 语句并从 SQL 查询中获取结果，请使用数据库游标。调用 `con.cursor()` 创建游标。	`cursor_obj = con.cursor()`
execute()	`cursor_obj.execute()`	The `execute` method in Python's SQLite library allows to perform SQL commands, including retrieving data from a table using a query like "Select * from table_name." When you execute this command, the result is obtained as a collection of table data stored in an object, typically in the form of a list of lists. Python 的 SQLite 库中的 `execute` 方法允许执行 SQL 命令，包括使用查询（如 "Select * from table_name."）从表中检索数据。当你执行此命令时，结果作为存储在对象中的表格数据集合获得，通常以列表的列表形式存在。	`cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')`
fetchall()	`cursor_obj.fetchall()`	The `fetchall()` method in Python retrieves all the rows from the result set of a query and presents them as a list of tuples. Python 中的 `fetchall()` 方法从查询的结果集中检索所有行，并将它们以元组列表的形式呈现。	`statement = '''SELECT * FROM INSTRUCTOR'''` `cursor_obj.execute(statement)` `output_all = cursor_obj.fetchall()` `for row_all in output_all:` `print(row_all)`
fetchmany()	`cursor_obj.fetchmany()`	The `fetchmany()` method retrieves the subsequent group of rows from the result set of a query rather than just a single row. To fetch a few rows from the table, use fetchmany(numberofrows) and mention how many rows you want to fetch. `fetchmany()` 方法从查询的结果集中检索后续的若干行，而不是单行。要从表中检索几行，使用 fetchmany(numberofrows)并指定您想检索的行数。	`statement = '''SELECT * FROM INSTRUCTOR'''` `cursor_obj.execute(statement)` `output_many = cursor_obj.fetchmany(2)` `for row_many in output_many:` `print(row_many)`
read_sql_query()	`read_sql_query()`	`read_sql_query()` is a function provided by the Pandas library in Python, and it is not specific to MySQL. It is a generic function used for executing SQL queries on various database systems, including MySQL, and retrieving the results as a Pandas DataFrame. `read_sql_query()` 是 Python Pandas 库提供的函数，并非特定于 MySQL。它是一个通用函数，用于在多种数据库系统（包括 MySQL）上执行 SQL 查询，并将结果作为 Pandas DataFrame 返回。	`df = pd.read_sql_query("select * from instructor;", conn)`
shape	`dataframe.shape`	It provides a tuple indicating the shape of a DataFrame or Series, represented as (number of rows, number of columns). 它提供表示 DataFrame 或 Series 形状的元组，格式为（行数，列数）。	`df.shape`
close()	`con.close()`	`con.close()` is a method used to close the connection to a MySQL database. When called, it terminates the connection, releasing any associated resources and ensuring the connection is no longer active. This is important for managing database connections efficiently and preventing resource leaks in your MySQL database interactions. `con.close()` 是一个用于关闭与 MySQL 数据库连接的方法。当被调用时，它会终止连接，释放任何相关资源，并确保连接不再处于活动状态。这对于高效管理数据库连接和防止 MySQL 数据库交互中的资源泄漏非常重要。	`con.close()`
CREATE TABLE	`CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ... );`	The `CREATE TABLE` statement is used to define and create a new table within a database. It specifies the table's name, the structure of its columns (including data types and constraints), and any additional properties such as indexes. This statement essentially sets up the blueprint for organizing and storing data in a structured format within the database. `CREATE TABLE` 语句用于在数据库中定义和创建一个新表。它指定了表的名称、其列的结构（包括数据类型和约束），以及任何附加属性（如索引）。该语句本质上为在数据库中以结构化格式组织和存储数据设置了蓝图。	`CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES (` `country VARCHAR(50),` `first_name VARCHAR(50),` `last_name VARCHAR(50),` `test_score INT` `);`
barplot()	`seaborn.barplot(x="x-axis_variable", y="y-axis_variable", data=data)`	`seaborn.barplot()` is a function in the Seaborn Python data visualization library used to create a bar plot, also known as a bar chart. It is particularly used to display the relationship between a categorical variable and a numeric variable by showing the average value for each category. `seaborn.barplot()` 是 Seaborn Python 数据可视化库中的一个函数，用于创建条形图，也称为柱状图。它特别用于通过显示每个类别的平均值来展示分类变量和数值变量之间的关系。	`import seaborn` `seaborn.barplot(x='Test_Score',y='Frequency', data=dataframe)`
read_csv()	`df = pd.read_csv('file_path.csv')`	`read_csv()` is a function in Python's Pandas library used for reading data from a Comma-Separated Values (CSV) file and loading it into a Pandas DataFrame. It's a common method for working with tabular data stored in CSV format `read_csv()` 是 Python 的 Pandas 库中的一个函数，用于从逗号分隔值（CSV）文件中读取数据并将其加载到 Pandas DataFrame 中。它是处理存储在 CSV 格式中的表格数据的一种常用方法。	`import pandas` `df = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9xf.csv')`
to_sql()	`df.to_sql('table_name', index=False)`	`df.to_sql()` is a method in Pandas, a Python data manipulation library used to write the contents of a DataFrame to a SQL database. It allows to take data from a DataFrame and store it structurally within a SQL database table. `df.to_sql()` 是 Pandas（一个 Python 数据操作库）中的一个方法，用于将 DataFrame 的内容写入 SQL 数据库。它允许将 DataFrame 中的数据结构化地存储在 SQL 数据库表中。	`import pandas` `df = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9xf.csv')` `df.to_sql("chicago_socioeconomic_data", con, if_exists='replace', index=False,method="multi")`
read_sql()	`df = pd.read_sql(sql_query, conn)`	`read_sql()` is a function provided by the Pandas library in Python for executing SQL queries and retrieving the results into a DataFrame from an SQL database. It's a convenient way to integrate SQL database interactions into your data analysis workflows. `read_sql()` 是 Pandas 库在 Python 中提供的一个函数，用于执行 SQL 查询并将结果从 SQL 数据库检索到 DataFrame 中。它是将 SQL 数据库交互集成到数据分析工作流程的一种便捷方式。	`selectQuery = "select * from INSTRUCTOR"` `df = pandas.read_sql(selectQuery, conn)`

Db2

Topic 主题	Syntax 语法	Description 描述	Example 示例
connect()	`conn = ibm_db.connect('DATABASE=dbname; HOST=hostname;PORT=port;UID=username; PWD=password;', '', '')`	`ibm_db.connect()` is a Python function provided by the ibm_db library, which is used for establishing a connection to an IBM Db2 or IBM Db2 Warehouse database. It's commonly used in applications that need to interact with IBM Db2 databases from Python. `ibm_db.connect()` 是由 ibm_db 库提供的 Python 函数，用于建立与 IBM Db2 或 IBM Db2 Warehouse 数据库的连接。它通常用于需要从 Python 与 IBM Db2 数据库进行交互的应用程序中。	`import ibm_db` `conn = ibm_db.connect('DATABASE=mydb;HOST=example.com;PORT=50000;UID=myuser;PWD=mypassword;', '', '')`
server_info()	`ibm_db.server_info()`	`ibm_db.server_info(conn)` is a Python function provided by the `ibm_db` library, which is used to retrieve information about the IBM Db2 server to which you are connected. `ibm_db.server_info(conn)` 是由 `ibm_db` 库提供的 Python 函数，用于获取您连接的 IBM Db2 服务器的信息。	`server = ibm_db.server_info(conn)` `print ("DBMS_NAME: ", server.DBMS_NAME)` `print ("DBMS_VER: ", server.DBMS_VER)` `print ("DB_NAME: ", server.DB_NAME)`
close()	`con.close()`	`con.close()` is a method used to close the connection to a db2 database. When called, it terminates the connection, releasing any associated resources and ensuring the connection is no longer active. This is important for managing database connections efficiently and preventing resource leaks in your db2 database interactions. `con.close()` 是用于关闭与 db2 数据库连接的方法。当被调用时，它会终止连接，释放任何相关资源，并确保连接不再处于活动状态。这对于高效管理数据库连接和防止 db2 数据库交互中的资源泄漏非常重要。	`con.close()`
exec_immediate()	`sql_statement = "SQL statement goes here"` `stmt = ibm_db.exec_immediate(conn, sql_statement)`	`ibm_db.exec_immediate()` is a Python function provided by the ibm_db library, which is used to execute an SQL statement immediately without the need to prepare or bind it. It's commonly used for executing SQL statements that don't require input parameters or don't need to be prepared in advance. `ibm_db.exec_immediate()` 是由 ibm_db 库提供的 Python 函数，用于立即执行 SQL 语句，无需准备或绑定。它通常用于执行不需要输入参数或不需预先准备的 SQL 语句。	`# Lets first drop the table INSTRUCTOR in case it exists from a previous attempt.` `dropQuery = "drop table INSTRUCTOR"` `dropStmt = ibm_db.exec_immediate(conn, dropQuery)`

Author(s) 作者

Abhishek Gagneja 阿比什克·甘格贾