Python Operation MySQL Storage

In Python 2, the library that connects MySQL mostly uses mysqldb, but the library's official does not support Python 3, so the recommended library here is pymysql. In this section, we'll explain how to use Pymysql to manipulate MySQL databases. 1. Preparation before you begin, make sure that you have installed the MySQL database and that it will work properly, and that you need to install the Pymysql library. 2. Connect to the database here, first try connecting to the database. Assuming that the current MySQL is running locally, the username is root, the password is 123456, and the running port is 3306. Here we use Pymysql to connect MySQL first, then create a new database, called spiders, the code is as follows: Import pymysqldb = pymysql.connect (host= ' localhost ', user= ' root ', Password= ' 123456 ', port=3306) cursor = Db.cursor () cursor.execute (' SELECT VERSION () ') data = Cursor.fetchone () print (' Database version: ', Data ' Cursor.execute ("CREATE DATABASE spiders DEFAULT CHARACTER SET UTF8") db.close () operation results are as follows: Database Version: (' 5.6.22 ',) Here you declare a MySQL Connection object db via the Pymysql Connect () method, and you need to pass in the MySQL run host (that is, IP). Because MySQL is running locally, localhost is passed in. If MySQL is running remotely, its public IP address is passed in. The following parameters user is the username, password is the password, port is the ports (default is 3306). After the connection succeeds, the cursor () method needs to be called to get the operation cursor of MySQL and execute the SQL statement using the cursor. Here we execute two sentences of SQL, executed directly with the Execute () method. The first sentence of SQL is used to get the current version of MySQL, and then call the Fetchone () method to get the first data, you get the version number. The second sentence SQL executes the database creation operation, the database name is spiders, the default code is UTF-8. Because the statement is not a query statement, the database spiders is created successfully after executing directly. Next, use this database for subsequent operations. 3. Create a table in general, create dataThe library operation only needs to be executed once. Of course, we can also create a database manually. Later, our operations are performed on the Spiders database. After you create the database, you need to specify an additional parameter db when you connect. Next, create a new data table students, where you execute the SQL statement that creates the table. 3 fields are specified here, as shown in the following structure. Field name meaning type ID number varcharname name varcharage age int The example code for creating the table is as follows: Import pymysqldb = pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123456 ', port=3306, db= ' spiders ') cursor = db.cursor () sql = ' CREATE TABLE IF not EXISTS students (ID VARCHAR (2 Not NULL, name VARCHAR (255) is not NULL, the age INT is not NULL, PRIMARY KEY (id)) ' cursor.execute (SQL) Db.close () is run, we create a named Students's data sheet. Of course, for demonstration purposes, only the simplest few fields are specified here. In fact, during a crawler, we design specific fields based on the crawl results. 4. Inserting data The next step is to insert data into the database. For example, if a student's information is crawled here, the number is 20120001, the name is Bob, and the age is 20, how do you insert the data into the database? The sample code is as follows: import pymysqlid = ' 20120001 ' user = ' Bob ' age = 20db = Pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123 456 ', port=3306, db= ' spiders ') cursor = db.cursor () sql = ' INSERT into students (ID, name, age) values (%s,%s,%s) ' Try:cu Rsor.execute (SQL, (ID, user, age)) Db.commit () Except:db.rollback () Db.close () Here first constructs an SQL statement whose value values are not constructed with string concatenation, as : sqL = ' INSERT into students (ID, name, age) VALUES (' + ID + ', ' + name + ', ' + Age + ') ' are cumbersome and not intuitive, so we chose to do this directly with the format character%s. There are several values to write a few%s, we only need to pass the SQL statement in the first argument of the Execute () method, and value value is passed through a uniform tuple. This way can avoid the trouble of string stitching, but also avoid the problem of quotation marks conflict. It is worth noting later that the commit () method of the DB object is required to implement the data insertion, which is the way to actually commit the statement to the database execution. For data insertion, update, and delete operations, this method needs to be called to take effect. Next, we add a layer of exception handling. If execution fails, call rollback () to perform a data rollback, which is equivalent to nothing has happened. This involves a matter of affairs. The transaction mechanism ensures the consistency of the data, which either happens or does not occur. For example, inserting a piece of data, there is no case of inserting half, either inserting it all or not inserting it, this is the atomicity of the transaction. In addition, transactions have 3 properties-consistency, isolation, and persistence. These 4 properties are often called acid properties, as shown in the following table. The attribute Interpretation atomicity (atomicity) transaction is an indivisible unit of work in which all operations included in a transaction are either done or not consistent (consistency) transactions must change the database from one state of consistency to another. Consistency and atomicity are closely related to isolation (isolation) The execution of a transaction cannot be disturbed by other transactions, that is, the operations within a transaction and the data used are isolated from other transactions that are concurrent. Each transaction that executes concurrently cannot interfere with one another persistent (durability) persistence is also known as permanence (permanence), which means that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it the INSERT, update, and delete operations are actions that make changes to the database, and the change operation must be a transaction, so the standard notation for these operations is: Try:cursor.execute (SQL) Db.commit () Except:db.rollback () This ensures consistency of the data. The commit () and rollback () methods here provide support for the implementation of the transaction. The above data insertions are implemented by constructing SQL statements, but it is clear that there is an extremely inconvenient place, such as a sudden increase in the gender field gender, when sThe QL statement needs to be changed to: INSERT into students (ID, name, age, gender) values (%s,%s,%s,%s) corresponding tuple parameters need to be changed to: (ID, name, age, gender) This is obviously not what we want. 。 In many cases, the effect that we want to achieve is that the insertion method does not have to be changed, and it is a common method, just to pass in a dynamically changing dictionary. For example, construct such a dictionary: {' id ': ' 20120001 ', ' name ': ' Bob ', ' Age ': 20} Then the SQL statements are dynamically constructed from the dictionary and the tuples are dynamically constructed to implement a generic insertion method. So, here we need to rewrite the insertion method: data = {' id ': ' 20120001 ', ' name ': ' Bob ', ' age ': 20}table = ' students ' keys = ', '. Join (Data.key s ()) values = ', '. Join (['%s '] * LEN (data)) sql = ' INSERT into {table} ({keys}) ' VALUES ({values}) '. Format (table=table, keys=k     Eys, Values=values) try:if cursor.execute (SQL, tuple (data.values ())): Print (' successful ') Db.commit () except: Print (' Failed ') Db.rollback () Db.close () The data we pass in here is a dictionary and defines it as a data variable. The table name is also defined as a variable table. Next, you need to construct a dynamic SQL statement. First, you need to construct the inserted field ID, name, and age. Just take the key name of data and then separate it with a comma. So ', '. The result of join (Data.keys ()) is ID, name, age, and then you need to construct more than one%s as a placeholder, with several fields constructed. For example, there are three fields and you need to construct%s,%s,%s. This first defines the array with length 1 ['%s '], then expands it to ['%s ', '%s ', '%s '] with multiplication, and then calls the join () method, which eventually becomes%s,%s,%s. Finally, we re-use the format () side of the stringThe method constructs the table name, field name, and placeholder. The final SQL statement is dynamically constructed: INSERT into students (ID, name, age) VALUES (%s,%s,%s) Finally, pass in the SQL variable for the first parameter of the Execute () method. The second parameter is passed into the tuple constructed by the key value of data, and the data can be inserted successfully. In this way, we have implemented a method of inserting data into a dictionary without having to modify the SQL statement and insert operation. 5. Updating the Data Data Update operation is actually the execution of the SQL statement, the simplest way is to construct an SQL statement, and then execute: sql = ' update students SET age =%s WHERE name =%s ' Try:cursor.execute (SQL, (+, ' Bob ')) Db.commit () Except:db.rollback () Db.close () Here also constructs the SQL in the placeholder way, then executes the Execute () method, passing in the tuple as the parameter, also executes the commit () method to perform the operation. You can use this method completely if you want to do a simple data update. However, in the actual data fetching process, the need to insert data in most cases, but we are concerned about the occurrence of duplicate data, if there is, we want to update the data instead of repeating the save. In addition, as previously mentioned in the dynamic construction of SQL problem, so here can be implemented a deduplication method, if the data exists, then update the data, if the data does not exist, insert data. In addition, this approach supports flexible dictionary-passing values. Examples are as follows: Data = {' id ': ' 20120001 ', ' name ': ' Bob ', ' age ': 21}table = ' students ' keys = ', '. Join (Data.keys ()) values = ', '. Join (['%s '] * LEN (data)) sql = ' INSERT into {table} ({keys}) VALUES ({VALUES}) on DUPLICATE KEY UPDATE '. Format (table=ta BLE, Keys=keys, values=values) update = ', '. Join (["{Key} =%s". Format (Key=key) for key in data]) SQL + = Updatetry:if CU Rsor.execute (SQL, Tuple (Data.values ()): Print (' successful ') Db.commit () except:print (' Failed ') db.rollback () Db.clos E () The SQL statement constructed here is actually an INSERT statement, but we added the on DUPLICATE KEY UPDATE later. This line of code means that if the primary key already exists, the update operation is performed. For example, we pass in the data ID is still 20120001, but the age changes, from 20 to 21, when this data is not inserted, but directly update the data ID 20120001. The complete SQL construct is this: INSERT into students (ID, name, age) VALUES (%s,%s,%s) on DUPLICATE KEY UPDATE id =%s, name =%s, age =% S becomes 6%s. So the second parameter tuple of the Execute () method later needs to be multiplied by 2 to become twice times the original. As a result, we can insert the data when the primary key does not exist, and there is the ability to update the data. 6. Deleting a data delete operation is relatively straightforward, just using the DELETE statement, you only need to specify the target table name and deletion criteria to be deleted, and you still need to use the commit () method of db to take effect. The example is as follows: Table = ' students ' condition = ' age > ' sql = ' DELETE from {table} WHERE {condition} '. Format (table=table, Conditi on=condition) try:cursor.execute (SQL) Db.commit () Except:db.rollback () Db.close () because there are a variety of delete conditions, operators are greater than, less than, equal to, like, etc., condition Connectors have and, or, and so on, so no longer continue to construct complex judgment conditions. The condition is passed directly as a string to implement the delete operation. 7. Query data after the insertion, modification and deletion of operations, but also left a very important operation, that is the query. The query uses the SELECT statement, as shown in the following example: sql = ' SELECT * ' from students WHERE Age >= ' Try:cursor.execute (SQL) PrinT (' Count: ', cursor.rowcount) one = Cursor.fetchone () print (' One: ', one) results = Cursor.fetchall () print (' Res Ults: ', results) print (' Results type: ', type (results)) ' for Row ' results:print (row) except:print (' Error ' The results of the operation are as follows: Count:4one: (' 20120001 ', ' Bob ', ') Results: ((' 20120011 ', ' Mary ', '), (' 20120012 ', ' Mike ', '), (' 20120013 ', ' Ja Mes ', ') Results Type: <class ' tuple ' > (' 20120011 ', ' Mary ', +) (' 20120012 ', ' Mike ', ') ' (' 20120013 ', ' James ', 22) Here we construct an SQL statement that queries students aged 20 and older and passes them on to the Execute () method. Note that the commit () method of the DB is no longer needed here. Next, call the cursor's RowCount property to get the number of bars for the query result, which is 4 in the current example. Then we call the Fetchone () method, this method can get the result of the first data, the return result is a tuple form, the tuple element order corresponds to the field one by one, that is, the first element is the first field ID, the second element is the second field name, and so on. Then we call the Fetchall () method, which can get all the data for the result. Then print out its result and type, which is a two-tuple, each element is a record, and we iterate over the output. But here's a question, which shows 3 data instead of 4, does the Fetchall () method not get all the data? This is because its internal implementation has an offset pointer that points to the result of the query, starting with the offset pointer pointing to the first data, and once after the pointer is shifted to the next piece of data, so that the next data is taken. We initially called the Fetchone () method so that the offset pointer of the result points to the next data, and the Fetchall () method returns all data that the offset pointer points to until the end, so the method gets only 3 of the results. In addition, IYou can also use the while loop plus Fetchone () method to get all the data, instead of Fetchall () all together. Fetchall () returns all the results in tuples, which can be expensive if the amount of data is large. Therefore, it is recommended to use the following method to fetch data: sql = ' SELECT * from students WHERE Age >= ' Try:cursor.execute (SQL) print (' Count: ', CURSOR.R Owcount) row = Cursor.fetchone () while Row:print (' Row: ', row) row = Cursor.fetchone () except:print (' Error ') so that every time a loop is made, the pointer offsets a single piece of data, which is easy and efficient to follow.

　　

Python Operation MySQL Storage