Python connects MySQL database (MySQL)

Before describing Python's operations in the database, let's briefly introduce the operating language in MySQL:

[[Email protected]2018- on- -]# Mysql-u Root-Penter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connectionIDIs194Server Version:5.6. -Source Distributioncopyright (c) -, the, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or Itsaffiliates. Other names trademarks of their respectiveowners. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement.mysql>Show Databses;mysql> CREATE TABLE Users (ID int(2) notNULLPrimary KEY Auto_increment,username varchar ( +), Passwordmysql>desc Users;mysql>Select*From Users;mysql> INSERT into Users (Username,password,email) VALUES ("Tiantian","tiange1003","[email protected]MySQL > INSERT into users (Username,password,email) VALUES ("zhiping","zhiping","[email protected]"); MySQL>Select* from users;

View Code

To install the Python module:

Yum Install Python-mysqldb

Operating the database in interactive mode:

 >>> import mysqldb  >>> conn = MySQLdb.connect (Host= " localhost  " , User= " root   ", passwd  =  tiange1003   ", Db="  tian   ", port=< Span style= "COLOR: #800080" >3306 , Charset= " utf8   ", Unix_socket="  /tmp/mysql.sock   ) 

Explain the meanings of the above commands one by one:
Host: The address of the MySQL database should be filled in after the equals sign, because the database is on this machine (also known as local), so use localhost, note the quotation marks. If the other server, here should be filled with IP address, generally small and medium-sized web sites, databases and programs are on the same server (computer), the use of lcalhost
User: Log in to the database username, where the general "root", or pay attention to the quotation marks. Of course, if it is a larger service, the database will provide a different user, then it can be changed to the appropriate user. However, different users may have different permissions, so in a program, if you want to manipulate the database, you should also be aware of the permissions that you have. Here with root, you can rest assured, what permissions are there. However, this is something that should be avoided in large systems.
passwd: Login mysql password for the user account above. My password in the above particle is "tiange1003", do not forget the quotation marks
DB: Just the database created by the Create command, the name of the database I created is "Tian", or the quotation marks. Custody If the database name is not this, write the name of the database you built.
Port: In general, the default port for MySQL is 3306, and when MySQL is installed on the server, in order to allow network access, the server provides a access port to him
CharSet: This setting, in many tutorials do not write, if the real data storage, the discovery of garbled. Here I will tian this database encoding to the UTF-8 format, so that the Chinese characters are allowed to be stored without garbled. Note that in MySQL setup, Utf-8 is written as UTF8, without the middle horizontal line. But in the beginning of the Python file and other places to set the encoding format, to write Utd-8, remember.

Note: Connect in the host, user, passwd, etc. can not write, only in the time of writing in accordance with the host, user, passwd, db (Can not write), port sequence can be written, note the port number port=3306 or do not omit the good, If there is no DB in front of Port, write directly 3306 return error.

As far as the database is concerned, it is necessary to operate it after the connection, but the current database named Tian is just an empty shelf, there is nothing to manipulate, to operate it, it must be built in the "table", what is the database table?
In a relational database, a database represents a collection of a series of two-dimensional arrays that represent and store the relationships between data objects. It consists of a vertical column and a horizontal row, such as a table with information about sitting in the name authors, each of which contains all the moles of a particular type of information, such as "Last Name", and each row contains all the information of a particular author: surname, name, address, etc.
For a particular database table, the number of columns is usually fixed beforehand, and the columns can be identified by column names. The number of rows can be changed at any time, and each line can usually be identified as a candidate key based on the data in one (or several) columns.

To create a database and insert data:

Mysql>Use Tian; Database changed MySQL>Show tables; Empty Set (0.00sec) shows if there are any data tables in this data with the show Tables command. Query results shown as empty MySQL> CREATE TABLE Users (ID int(2) notNULLPrimary KEY Auto_increment,username varchar ( +), password text,email text) default charset=UTF8; Query OK,0Rows Affected (0.02sec) The name of this data table is: Users, which contains the above fields, can look at the structure of this data table in the following way. MySQL>Show tables; +----------------+    |    Tables_in_tian | +----------------+    |    Users | +----------------+1RowinchSet (0.00sec) MySQL>desc users; +----------+-------------+------+-----+---------+----------------+    | Field | Type | Null | Key | Default |    Extra | +----------+-------------+------+-----+---------+----------------+    |ID|int(2)      | NO | PRI | NULL |    auto_increment | | Username | varchar +) |     YES | |                NULL |    | | password | Text |     YES | |                NULL |    | | email | Text |     YES | |                NULL |    | +----------+-------------+------+-----+---------+----------------+4RowsinchSet (0.00sec) shows the structure of the table users:ID: Each additional user, ID number automatically adds a username "Stored user name, type is varchar ( +Password: Store user password, type is text email: Store User's mailbox, type is text this structure and the above expected structure is the same, but this table does not have any data, is an empty table, you can check to see: MySQL >Select*from users; Empty Set (0.00sec) currently empty, in order to be able to manipulate the data table in Python later, you need to insert a point inside the information: MySQL>INSERT into users (Username,password,email) VALUES ("Tiantian","tiange1003","[email protected]"); Query OK,1Row affected (0.00sec) MySQL>Select*from users; +----+----------+------------+------------------------+    |ID| Username | password |    email | +----+----------+------------+------------------------+    |1| Tiantian | tiange1003 |    [Email protected] | +----+----------+------------+------------------------+1RowinchSet (0.00Sec

So far, the work in MySQL has been done, and the next step is to use Python:

>>>Import MySQLdb>>> conn = MySQLdb.connect (host="localhost", user="Root",passwd="tiange1003", db="Tian", port=3306, charset="UTF8", unix_socket='/tmp/mysql.sock')>>> cur =conn.cursor ()>>> Cur.execute ("INSERT into users (Username,password,email) values (%s,%s,%s)",("Chenchen","Chenchen","[email protected]"))>>> Conn.commit ()

Above is the Python interactive mode connection, the completion of the connection process, in fact, is to establish a mysqldb.connect () instance Object conn, then this object has what properties
Commit (): If the database table has been modified, commit to save the current data. Of course, if this user doesn't have permission, nothing will happen.
Rollback (): No permissions, cancel the current operation, or error
Curso (): Cursor pointer
After the connection is successful, start the operation. Note: MySQLdb uses cursors (pointers) to manipulate the database in the same way:
>>> cur = conn.cursor ()
Because the change module is actually called caps, so you need to get the current pointer to the database, which is to remind us, in the operation of the database, the pointer will move, if you move to the last piece of data, and then check, you can not find out what came.

Cursor how to execute the command:

Execute (Query,srgs): Executes a single SQL statement. Query is the SQL statement itself, and SRGs is the list of parameter values. The number of rows affected when the return value is executed

Executemany (Query,args): Executes a single SQL statement, but repeats the parameters in the list of parameters, and the return value is the number of rows affected

>>> Cur.execute ("insert into users (Username,password,email) values (%s,%s,%s)", ("chenchen","chenchen","[email protected]  "))1L

No error, and a "1L" result is returned, indicating that a row of records was successful. can go to MySQL inside the SQL language to view, but the view did not show success, the reason is that through the "Cur.execute ()" to the database operation, no error, completely correct, but not equal to the data has been submitted to the database, you must also use the " Mysql.connect "A property: Commit (), the data submitted up, but also in a timely manner" cur.execute () "operation, to speak the data submitted, must be executed:

>>> Conn.commit ()

And then go to MySQL in the use of SQL language query, the following results will appear:

Select * from users; ID 1 2 785298065 3 12345678@qq. com |+----+----------+------------+------------------------+3 in Set ( 0.00 sec)

Try to insert more than one command "Executemany (Query,args)"

>>> Cur.executemany ("INSERT into users (Username,password,email) values (%s,%s,%s)",(("Google","111222","[email protected]"),("Facebook","222333","[email protected]"),("GitHub","333444","[email protected]"),("Docker","444555","[email protected]")))4L

Then continue querying in MySQL using the SQL language:

Mysql>Select*from users;+----+----------+------------+------------------------+|ID| Username | password | Email |+----+----------+------------+------------------------+|1| Tiantian | tiange1003 | [Email protected] | |2| zhiping | zhiping |785298065@qq. com | |3| Chenchen | Chenchen |12345678@qq. com | |4| Google |111222| [Email protected] | |5| Facebook |222333| [Email protected] | |6| GitHub |333444| [Email protected] | |7| Docker |444555| [Email protected] |+----+----------+------------+------------------------+7RowsinchSet (0.00Sec

More than one record was successfully inserted. It is also important to note that in "Exectemany (Query,args)", query is still an SQL statement, but args is a tuplie at this time, and the element inside the tuple is also a tuple, Each tuple corresponds to a list of fields in the SQL statement. This sentence was actually executed several times. But the execution of the process does not show us.

Python connects MySQL database (MySQL)