This note is the learning note of coursera website course Databases and SQL for Data Science with Python
In this module you will learn the basic concepts related to using Python to connect to databases. In a Jupyter Notebook, you will create tables, load data, query data using SQL, and analyze data using Python
After completing this module, you will be able to explain the basic concepts related to connecting to a database using python. Then, you will create tables, load and query data using SQL statements in pymysql, and finally analyze the data using python. You will be able to explain the basic concepts involved in connecting Python applications to databases. Describe SQL API s and list some proprietary APIs used by popular SQL based DBMS systems
Learning objectives
- Describes concepts related to accessing databases using Python
- Demonstrates how to use SQL Magic to perform simplified database access from Python
- Demonstrates writing an SQL query and retrieving a result set from Python
- Show me how to connect to a database from a Jupyter notebook
- Demonstrates how to create tables and insert data from Python
How to access a database using Python
Some benefits of Python
1.Python ecosystem is very rich and provides easy-to-use data science tools. Some of the most popular packages are NumPy, panda, matplotlib and SciPy.
2.Python is easy to learn and simple in syntax.
3. Due to its open source nature, python has been ported to many platforms. All your Python programs can run these platforms on any of the following without any changes at all. Python supports relational database systems if you are careful and avoid using any system related functionality.
Writing Python code to access the database the existence of the python database API makes the operation easier. It is commonly referred to as the DB API, and detailed documentation related to Python is easily available.
Jupyter notebook
Jupyter notebook is an open source Web application that allows you to create and share documents, equations, visualization and narrative text containing real-time code. Here are some of the benefits of using a jupyter laptop.
1. The notebook supports more than 40 programming languages, including Python and R
2. Notebooks can be shared with others through e-mail, Dropbox, GitHub and Jupyter notebook viewer.
3. Your code can produce rich interactive output HTML, pictures, videos, LaTex and custom types. You can use Python, R, and Scala, and explore the same data through pandas, scikit learn, ggplot2, and TensorFlow.
Typical user access to the database, using Python code written on the Jupyter notebook and a web-based editor.
Python programs have a communication mechanism with DBMS
Python code connects to the database using API calls. We will explain the SQL API and Python DB API.
To pass SQL statements to the DBMS, the application calls functions in the API, which calls other functions to retrieve query results and status information from the DBMS.
We will call the API on the Jupiter notebook and use the python language to operate the database
Since you cannot connect to the experimental environment of the course website, the following steps are directly carried out in pycharm
reference: https://blog.csdn.net/weixin_42213622/article/details/86523400
python connecting to mysql database
1. Operation steps
pyton needs to install pymysql module before connecting to the database: pip install pymysql
Import pymysql module after installation: import pymysql
There are five steps to connect python to the database:
Step 1: connect to the database
Step 2: create cursor object. Calling the cursor() method of the connection object returns the cursor object, then invokes the execute() method of the cursor object to execute the query statement and query the database.
step3: add, delete, modify and query the database. Call the execute() method of the connection object to execute SQL statements and add, delete and modify data. If you add or modify data, you need to call the commit() method to submit the modification to take effect; The execute () method can also be used to execute DDL statements to create tables
step4: close cursor
step5: close connection
2. Cursor object
A cursor object can be obtained by calling the cursor() method of the connection object. What is a cursor? In fact, a cursor can be understood as a pointer, as shown in the following figure:
The pointer in the figure is the cursor. Assuming that the table on the right is the query result, you can call the fetchone() method of the cursor object to move the cursor pointer. Each time you call the fetchone() method, you can move the cursor pointer down one row. When you call the fetchone() method for the first time, you can move the cursor from the default position to the first row.
Several common methods of Cursor object:
execute() executes sql statements, usually query statements
fetchone() moves the cursor pointer down one row and returns the data of the current row
fetchall() takes all the results from the results and returns a list of all the results
close() closes the cursor
The row of data returned by the query is a tuple. For example, in the above code, print(row), where row is a tuple. Access each column of data through row[0] and row[1]. Index 0 corresponds to the id in the above code, index 1 corresponds to name, 2 corresponds to age, and so on
python must have a cursor object to send sql statements to the database and execute them
3. Specific code
import pymysql
# 1. Connect to the database,
conn = pymysql.connect(
host='localhost',
user='root',
password='****',
db='demo',
charset='utf8',
# autocommit=True, # If you insert data, do you want to submit it automatically? Consistent with conn.commit().
)
# ****In python, there must be a cursor object to send sql statements to the database and execute them
# 2. Create cursor object,
cur = conn.cursor()
# 3. Add, delete, modify and query the database
#
# 1). ************************ Create data table**********************************
try:
create_sqli = "create table hello (id int, name varchar(30));"
cur.execute(create_sqli)
#Note that the executed sql statement must be entered in double quotation marks
except Exception as e:
print("Failed to create data table:", e)
else:
print("Data table created successfully;")
# 2). ********************* Insert data****************************
try:
insert_sqli = "insert into hello values(2, 'fensi');"
cur.execute(insert_sqli)
except Exception as e:
print("Failed to insert data:", e)
else:
# If you are inserting data, you must submit the data, otherwise the data to be inserted cannot be found in the database;
conn.commit()
print("Insert data succeeded;")
# 3). ************************** Database query*****************************
sqli = "select * from customers;"
result = cur.execute(sqli) # By default, the query result set is not returned, and the number of data records is returned.
print(result)
print(cur.fetchone()) # 1). Get the next query result set;
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchmany(4)) # 2). Obtain several query result sets;
info = cur.fetchall() # 3). Get all query results
print(info)
print(len(info))
print(cur.rowcount) # 4). Returns the number of rows affected by executing sql statements
# # 3). ********************* Insert multiple pieces of data****************************
try:
info = [(i, "westos%s" %(i)) for i in range(100)]
# *********************The first way********************
# %s must add a string manually, otherwise it is a variable name and an error will be reported
insert_sqli = "insert into hello values(%d, '%s');"
for item in info:
print('insert sentence:', insert_sqli %item)
cur.execute(insert_sqli %item)
except Exception as e:
print("Failed to insert multiple pieces of data:", e)
else:
# If you are inserting data, you must submit the data, otherwise the data to be inserted cannot be found in the database;
conn.commit()
print("Insert multiple data successfully;")
# 4). Move cursor pointer
sqli = "select * from customers;"
result = cur.execute(sqli) # By default, the query result set is not returned, and the number of data records is returned.
print(result)
print(cur.fetchmany(3))
print("Moving pointer to start......")
cur.scroll(0, 'absolute')
print(cur.fetchmany(3))
print("Moving pointer to penultimate......")
print(cur.fetchall()) # Move to last
cur.scroll(-2, mode='relative')
print(cur.fetchall())
# 4. Close the cursor
cur.close()
# 5. Close the connection
conn.close()
be careful:
# Adding or modifying data will only take effect after submission conn.commit()
Another: simple code for SQLite database
It is a lightweight database and a relational database (RDBMS) management system. It is contained in a relatively small C library.
A simplified version of the code:
# Import module
import sqlite3
# Connect to the database and return the connection object
conn = sqlite3.connect("D:/my_test.db")
# Call the execute() method of the connection object to execute the SQL statement
# (a DDL statement is executed here to create a table called students_info)
conn.execute("""create table if not exists students_info (
id integer primary key autoincrement,
name text,
age integer,
address text)""")
# Insert a piece of data
conn.execute("insert into students_info (name,age,address) values ('Tom',18,'Beijing East Road')")
# Adding or modifying data will only take effect after submission
conn.commit()
# Call the cursor() method of the connection object to return the cursor object
cursor = conn.cursor()
# Call the execute() method of the cursor object to execute the query statement
cursor.execute("select * from students_info")
# After executing the query, the query results are saved to the cursor object, and the cursor object is invoked to get the query results.
# Here, the fetchall method is called to return a list. Tuples are stored in the list,
# Each tuple is a row of data in the data table
result = cursor.fetchall()
#Traverse all results and print
for row in result:
print(row)
#close
cursor.close()
conn.close()
4. Screenshot of operation results:
1. Create data table:
2. Insert data;
3. Insert multiple pieces of data (the first method);
4. Database query;
5. Move the cursor pointer
Get the field name and information of the table
First import the required modules
Then enter the main program part: this part is written with the with statement (the effect of the with statement is that the execution of the with statement ends. If it is successful, the changed data will be submitted. If it is unsuccessful, it will be rolled back.)
import time
import pymysql
conn = pymysql.connect(
host='localhost',
user='root',
password='*****',
db='demo',
charset='utf8',
# autocommit=True, # If you insert data, do you want to submit it automatically? Consistent with conn.commit().
) #Connect to database
# ******Determine whether to connect?
print(conn.open) # True
# 2. Create cursor object,
cur = conn.cursor()
# 3. Query the database
sqli = "select * from hello;"
result = cur.execute(sqli) # By default, the query result set is not returned, and the number of data records is returned.
# Show details for each column
des = cur.description
print("Table description:", des)
# Get header
print("Header:", ",".join([item[0] for item in des]))
# 4. Close cursor and connection
cur.close() #Close the cursor inside the with statement
conn.close() #Close pointer, outside with statement
Operation results:
Realization of bank transfer function based on mysql database
Considerations of bank transfer system mainly include:
1). Judge whether two bank card numbers exist?
2). Judge source_ Do you have enough money?
3). source_id deduction
4). target_id plus money
The code is as follows:
import pymysql
class TransferMoney(object):
# Construction method
def __init__(self, conn):
self.conn = conn
self.cur = conn.cursor()
def transfer(self, source_id, target_id, money):
if not self.check_account_avaialbe(source_id):
raise Exception("Account does not exist")
if not self.check_account_avaialbe(target_id):
raise Exception("Account does not exist")
if self.has_enough_money(source_id, money):
try:
self.reduce_money(source_id, money)
self.add_money(target_id, money)
except Exception as e:
print("Transfer failed:", e)
self.conn.rollback()
else:
self.conn.commit()
print("%s to%s transfer accounts%s Amount succeeded" % (source_id, target_id, money))
def check_account_avaialbe(self, acc_id):
"""Judge whether the account number exists. The parameter passed is the bank card number id"""
select_sqli = "select * from bankData where id=%d;" % (acc_id)
print("execute sql:", select_sqli)
res_count = self.cur.execute(select_sqli)
if res_count == 1:
return True
else:
# Raise exception ("account% s does not exist"% (acc_id))
return False
def has_enough_money(self, acc_id, money):
"""judge acc_id Amount on account> money"""
# Find acc_id storage amount?
select_sqli = "select money from bankData where id=%d;" % (acc_id)
print("execute sql:", select_sqli)
self.cur.execute(select_sqli) # ((1, 500), )
# Get the queried amount and amount;
acc_money = self.cur.fetchone()[0]
# judge
if acc_money >= money:
return True
else:
return False
def add_money(self, acc_id, money):
update_sqli = "update bankData set money=money+%d where id=%d" % (money, acc_id)
print("add money:", update_sqli)
self.cur.execute(update_sqli)
def reduce_money(self, acc_id, money):
update_sqli = "update bankData set money=money-%d where id=%d" % (money, acc_id)
print("reduce money:", update_sqli)
self.cur.execute(update_sqli)
# Deconstruction method
def __del__(self):
self.cur.close()
self.conn.close()
if __name__ == '__main__':
# 1. Connect to the database,
conn = pymysql.connect(
host='localhost',
user='root',
password='******',
db='demo',
charset='utf8',
autocommit=True, # If you insert data, do you want to submit it automatically? Consistent with conn.commit().
)
trans = TransferMoney(conn)
trans.transfer(13997, 13998, 200) # Transfer 200 from account 13997 to 13998
Create the corresponding table first:
During the test, first construct the data table bankData in the database, and add user information (including user account and amount in the account) in the data table as follows:
After running the code, the amount in the database changes, and 200 yuan is transferred from account 13997 to account 13998:
(transaction needs to be committed)
[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-5gcwmhb4-1628408165590) (C: \ users \ yunmu \ appdata \ roaming \ typora \ typora user images \ image-20210427192947760. PNG)]
summary
- We learned how to use Python to call mysql database. We need to use some APIs. Here we use the python mysql module
- We also learned how to use the Jupyter notebook
- Until we use python to operate the database, we need five steps: import the corresponding package; Connect to the database, create a database table, and create a cursor; Use the corresponding function execute to add, delete, modify and query the database; Commit the added or modified database and close the cursor and connection.
- We learned about cursors, which are used to access the contents of the database in turn
- We also applied a database application instance using python, the bank account transfer fund application based on mysql