With old Ziko python use Python operations database (1) _python

The database was already connected in the previous lecture. In the case of a database, the connection is then manipulated. However, the current name of the Qiwsirtest data is only empty shelf, there is nothing to operate, to operate it, you have to create a "table", what is the database table? The following excerpt word Wikipedia on the database table of the brief explanation, want to understand more, need reader in looking for some of the database tutorials and books to see.

In a relational database, a database table is a set of two-dimensional arrays that represent and store relationships between data objects. It consists of a vertical column and a horizontal row, for example, in a table named authors with author information, each column contains a specific type of information for all authors, such as "Last Name," and each row contains all the information for a particular author: Last name, first name, address, and so on.
For a particular database table, the number of columns is generally fixed beforehand, and the columns can be identified by column names. The number of rows can change at any time, dynamically, and each row can usually be identified by the data in one (or several) columns, called a candidate key.
I intend to create a table in Qiwsirtest that stores user names, user passwords, and user mailboxes, and its structure is shown in two-dimensional forms as follows:

Username Password Email
Qiwsir 123123 qiwsir@gmail.com

Specifically, here to simplify the details, highlighting the focus, the password is not encrypted, direct plaintext save, although this way is very unsafe. However, there are many websites still do so, the purpose of this is more hateful. Let me be here, just one damn time here.

To build a database table and insert data

In order to build this table in the database, you need to move into the mysql> interaction mode. The reason is that if qiwsirtest this room there is no similar furniture in the various database tables, even into the house there is no good operation of things, so need to go to the mysql> mode in the room to lay furniture.

Enter database interaction mode:

Copy Code code as follows:

qw@qw-latitude-e4300:~$ Mysql-u Root-p
Enter Password:

To invoke an already established database: Qiwsirtest

Copy Code code as follows:

mysql> use qiwsirtest;
Database changed
Mysql> Show tables;
Empty Set (0.00 sec)

Use the show tables command to display whether there are any data tables in the database. The query results appear empty.

The following command is used to create a data table with the contents of the table as described above.

Copy Code code as follows:

Mysql> CREATE table users (ID int (2) NOT NULL PRIMARY key auto_increment,username varchar (), password text,email text) d Efault Charset=utf8;
Query OK, 0 rows affected (0.12 sec)

The data table name is: Users, which contains the fields above, and you can look at the structure of the datasheet in the following way.

Copy Code code as follows:

Mysql> Show tables;
+----------------------+
| Tables_in_qiwsirtest |
+----------------------+
| Users |
+----------------------+
1 row in Set (0.00 sec)

The query shows that in the Qiwsirtest database, there is already a table with the name: users.

Copy Code code as follows:

mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID | Int (2) | NO | PRI | NULL | auto_increment |
| Username | varchar (40) |     YES | |                NULL | |
| password | Text |     YES | |                NULL | |
| email | Text |     YES | |                NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in Set (0.00 sec)

Displays the structure of the table users:

ID: Each additional user, ID number automatically add one.
Username: Store user name, type is varchar (40)
Password: Store user password, type is text
Email: Store User's mailbox, type is text
Special reminder: Here, I do not inject each field does not have to empty and so on, in real development, perhaps must let username and password not be empty.

This structure is the same as the desired structure above, except that the table does not have any data, it is an empty table. You can check it out:

Copy Code code as follows:

Mysql> select * from users;
Empty Set (0.01 sec)

Now the table is empty, in order to be able to use Python to manipulate the data table, you need to insert a point of information inside, just insert one bar.

Copy Code code as follows:

mysql> INSERT into users (Username,password,email) VALUES ("Qiwsir", "123123", "qiwsir@gmail.com");
Query OK, 1 row affected (0.05 sec)

Mysql> select * from users;
+----+----------+----------+------------------+
| ID | Username | password | email |
+----+----------+----------+------------------+
| 1 | Qiwsir | 123123 | qiwsir@gmail.com |
+----+----------+----------+------------------+
1 row in Set (0.00 sec)

So far, the work in mysql> has been done, and the next step is to use Python.

Python Operations Database

To operate the database, you need to connect it first. The last talk reader connected, but then you shut down the Python interaction mode, so you have to reconnect. This is also the disadvantage of interactive mode. But it's intuitive to operate here, so put up with it for a while and then explain how to do it automatically in the program.

Copy Code code as follows:

>>> Import MySQLdb
>>> conn = MySQLdb.connect (host= "localhost", user= "root", passwd= "123123", db= "Qiwsirtest", charset= "UTF8")

The process of completing the connection, in fact, is to create a mysqldb.connect () instance Object conn, then what are the attributes of this object?

Commit (): If the database table has been modified, commit to save the current data. Of course, if this user does not have permission to do it, nothing will happen.
Rollback (): If you have permission, cancel the current operation, otherwise the error.
Cursor ([Cursorclass]): Cursor pointer. Detailed below.
After the connection is successful, start the operation. Note: MySQLdb manipulate the database in a cursor (pointer) cursor way:

Copy Code code as follows:

>>> cur = conn.cursor ()

Because the bottom of the module is actually called CAPI, you need to get the current pointer to the database. This reminds us, in the operation of the database, the pointer will move, if moved to the last one of the database, and then check, you can not find what came. Look at the following examples to understand.

The following methods are provided by cursor (), mainly by:

Execute command
Receive results

Cursor how to execute a command:

Execute (query, args): Executes a single SQL statement. Query is the SQL statement itself, args as a list of parameter values. The returned value is the number of rows affected after execution.
Executemany (query, args): Executes a single SQL statement, but repeats the parameters in the parameter list, returning the value to the number of rows affected
For example, to insert a record in the datasheet users, make: Username= "Python", password= "123456", email= "python@gmail.com", doing this:

Copy Code code as follows:

>>> Cur.execute (INSERT into users (Username,password,email) values (%s,%s,%s), ("Python", "123456", " Python@gmail.com "))
1L

There is no error, and a "1L" result is returned, indicating that one row of records was successful. You may wish to use the "mysql>" Interactive Way to view:

Copy Code code as follows:

Mysql> select * from users;
+----+----------+----------+------------------+
| ID | Username | password | email |
+----+----------+----------+------------------+
| 1 | Qiwsir | 123123 | qiwsir@gmail.com |
+----+----------+----------+------------------+
1 row in Set (0.00 sec)

Gee, that's weird. Why didn't you see the added one? What's wrong? But there is no error on the top.

Here, especially please reader note that the "Cur.execute ()" After the operation of the database, no error, completely correct, but not equal to the data has been submitted to the database, you must also use the "MySQLdb.connect" a Property: Commit (), The data is submitted, that is, the "cur.execute ()" operation, to submit data, must be performed:

Copy Code code as follows:

>>> Conn.commit ()

Run "SELECT * from users" in "mysql>" to try:

Copy Code code as follows:

Mysql> select * from users;
+----+----------+----------+------------------+
| ID | Username | password | email |
+----+----------+----------+------------------+
| 1 | Qiwsir | 123123 | qiwsir@gmail.com |
| 2 | Python | 123456 | python@gmail.com |
+----+----------+----------+------------------+
2 rows in Set (0.00 sec)

Good,very good. Sure enough. This is like writing a text, writing to the text, does not mean that the text has been left in the text file, you must perform "ctrl-s" to save. That is, when you manipulate a database through Python, after executing various SQL statements with execute (), to save the effect that has been executed, you must run "commit ()" and remind that this property is an instance of "MySQLdb.connect ()".

Try the Command "Executemany (Query,args)" that inserts more than one.

Copy Code code as follows:

>>> Cur.executemany ("INSERT into users (Username,password,email) VALUES (%s,%s,%s)", ("Google", "111222", " G@gmail.com "), (" Facebook "," 222333 "," F@face.book "), (" GitHub "," 333444 "," git@hub.com "), (" Docker "," 444555 "," " Doc@ker.com "))
4L
>>> Conn.commit ()

To "mysql>" inside See the result:

Copy Code code as follows:

Mysql> select * from users;
+----+----------+----------+------------------+
| ID | Username | password | email |
+----+----------+----------+------------------+
| 1 | Qiwsir | 123123 | qiwsir@gmail.com |
| 2 | Python | 123456 | python@gmail.com |
| 3 | Google | 111222 | g@gmail.com |
| 4 | Facebook | 222333 | F@face.book |
| 5 | GitHub | 333444 | git@hub.com |
| 6 | Docker | 444555 | doc@ker.com |
+----+----------+----------+------------------+
6 rows in Set (0.00 sec)

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

It's already plugged in, and then you can have more action. Let's take a look at the next lecture.