Python Operations Database

Database Programming

From the previous we know that the database concept contains database files, servers, and database client clients that we have used before, such as navicat/mysql-client and other programs.

Question: How do I use a client to insert 100000 rows of data into a database?

You will find that it is almost impossible to complete this task by using a client before, because it is impossible to construct the SQL that inserts 10w rows of data. However, if we have a function to insert a row of data, with the powerful features of the program-Repeat, it is easy to take 10w rows of data revenue. This way, through the use of program code to connect to the database server, through the interaction with the server to complete the database additions and deletions to change the way, called database programming.

And now learning Pymysql is a kind of client.

working with MySQL steps in Python

How to understand the connection connection and cursors cursor connection is like a highway that connects the origin and the destination. Cursor is like a truck on a freeway-pull we use cursors to complete the operation of the data when we finish the operation, we can stop the van, Then the road stops using it again.

Use of Pymysql

Introducing Modules

from pymysql import connect

Connection Object

• Used to establish a connection to the database call the Connect () method in the Pymysql module

conn=connect(参数列表)* 参数host：连接的mysql主机，如果本机是‘localhost‘* 参数port：连接的mysql主机的端口，默认是3306* 参数database：数据库的名称* 参数user：连接的用户名* 参数password：连接的密码* 参数charset：通信采用的编码方式，推荐使用utf8

• Close connectionconn.close()

• Submit dataconn.commit()

• Revoke dataconn.rollback()

• cur = conn.cursor()a Cursor object is returned by a connection-fetching cursors for executing the SQL statement and obtaining the result

Cursor Cursor Object

• Get Cursor Object

    # 调用Connection对象的cursor()方法      cur =conn.cursor()

Purpose: Execute SQL statements (the most frequently used statements are SELECT, INSERT, UPDATE, DELETE)

• Executing SQL statements using cursors

execute(operation [, parameters ])Executes an SQL statement that returns the number of rows affected, primarily for INSERT, UPDATE, DELETE statements, or create, alter, DROP, and other statements

• Close Cursorscur.close()

• Get a piece of the result set

  cur.fetchone()Returns a tuple of the form (1, ' hexyl ', 18)

• Gets all of the data in the result set

  cur.fetchall()When executing a query, get all the rows of the result set, one row to form a tuple, and then assemble the elements into a tuple to return the shape ((1, ' Gongsun ', 20), (2, ' n ', 18))

pymysql Complete data query

import pymysql# 创建和数据库服务器的连接 服务器地址　　　端口　　　　用户名　　　　　密码  数据库名 通信使用字符和数据库字符集一致conn = pymysql.connect(host=‘localhost‘, port=3306, user=‘root‘, password=‘mysql‘,database=‘python_test_1‘, charset=‘utf8‘)# 获取游标cursor = conn.cursor()# 执行ＳＱＬ语句 返回值就是ＳＱＬ语句在执行过程中影响的行数sql = """select * from hero;"""row_count = cursor.execute(sql)print("ＳＱＬ语句执行影响的行数%d" % row_count)# 取出结果集中一行  返回的结果是一行　(1, ‘妲己‘, 2)# print(cursor.fetchone())# 取出结果集中的所有数据　　返回　((一行数据),(),())# ((1, ‘妲己‘, 2), (2, ‘李白‘, 1), (3, ‘程咬金‘, 3), (4, ‘亚瑟‘, 5), (5, ‘荆轲‘, 99))for line in cursor.fetchall():    print(line)# 关闭游标cursor.close()# 关闭连接conn.close()

Pymysql completion of the database additions and deletions to change

import pymysqlconn = pymysql.connect(host=‘127.0.0.1‘,port=3306,user=‘root‘,password=‘mysql‘,db=‘python_test_1‘, charset=‘utf8‘)# 通过连接获取游标cursor = conn.cursor()# sql = "delete from hero where id = 5;"# sql = insert into hero (name) values (‘西部大坏蛋孙悟空‘);sql = "update hero set kongfuid=444 where id = 4;"row_count = cursor.execute(sql)print("ＳＱＬ语句执行影响的行数%d" % row_count)# 提交数据到数据库# conn.commit()# 回滚数据到什么都不做的状态 即撤销刚刚的修改conn.rollback()# 关闭游标和连接cursor.close()conn.close()

About committing commit commit commits the modification to the database, saving the changes

Note that data in Pymysql needs to be manually committed to be saved to the database

parameterized lists prevent SQL injection

What is the reason for SQL injection: The background is a string concatenation of user-submitted malicious data and SQL, which affects the semantics of SQL statements. The result is a data disclosure phenomenon. If the SQL statement is prevented from being parameterized, all data parameters of the SQL statement are passed to the second parameter of the Execute function in a list

note

   * Here is different from the Python string formatting, must all use the%s placeholder * All parameters need not be quoted in addition to the placeholder   

   from Pymysql import connectdef main (): Find_name = input ("Please enter item Name:") # Create Co Nnection Connection conn = connect (host= ' localhost ', port=3306,user= ' root ', password= ' MySQL ', database= ' Jing_dong ', charset= ' UTF8 ') # Gets the cursor object CS1 = Conn.cursor () # # non-Secure Way # # enter "or 1=1 or" (double quotes also enter) # sql = ' SELECT * FROM Goods where name= "%s" '% Find_name # print ("" "" sql===>%s<==== "" "% sql) # # Executes the SELECT statement and returns the number of rows affected: Querying all data # Co UNT = Cs1.execute (SQL) # Secure Way # Constructs a parameter list params = [Find_name] # Executes a SELECT statement and returns the number of rows affected: Query all data count = Cs1.exe Cute (' select * from goods where name=%s ', params) # Note: # If you have more than one parameter, you need to parameterize # then the params = [value 1, value 2 ...], at this point the SQL statement has    Multiple%s to # Print the affected number of rows print (count) # Gets the result of the query # result = Cs1.fetchone () result = Cs1.fetchall () # Print the results of the query Print (Result) # Close Cursor Object Cs1.close () # Close Connection Object Conn.close () if __name__ = = ' __main__ ': Main ()   

Python Operations Database