Familiar with Python, using Python for database operations (1), and learning python

Familiar with Python, using Python for database operations (1), and learning python

The database has been connected in the previous lecture. As far as the database is concerned, it must be operated after the connection. However, the data named qiwsirtest is just an empty shelf and there is no operation. to operate on it, you must create a "table" in it. What is a database table? The following is a brief description of the Database Table in Wikipedia. For more information, see database tutorials and books.

In relational databases, database tables are a collection of two-dimensional arrays to represent the relationship between data objects and stored data objects. It consists of vertical columns and horizontal rows. For example, in a table named authors about the author information, each column contains a specific type of information of all authors, for example, "Last Name", while each line contains all the information of a specific Author: Last Name, name, address, and so on.
For a specific database table, the number of columns is generally fixed in advance, and the columns can be identified by column names. The number of rows can change at any time and dynamically. Each row can usually be identified based on data in one (or several) column, which is called a candidate key.
I plan to create a table in qiwsirtest that stores the user name, user password, and user email address. The two-dimensional table structure is shown as follows:

Username password email
Qiwsir 123123 qiwsir@gmail.com

Note: In order to simplify the details and highlight the key points, the password is not encrypted and stored in plaintext directly, although this method is not safe. However, many websites are still doing this, and the purpose of doing so is rather hateful. Let me be here, just once here.

Create a database table and insert data

To create this table in the database, you must go to mysql> interactive mode. The truth is that if qiwsirtest does not have any database tables similar to furniture in the room, there is nothing to do even if it enters the room, therefore, you need to place furniture in the room in mysql> mode.

Enter the database interaction mode:

Copy codeThe Code is as follows:
Qw @ qw-Latitude-E4300 :~ $ Mysql-u root-p
Enter password:

Call the created database: qiwsirtest

Copy codeThe Code is as follows:
Mysql> use qiwsirtest;
Database changed
Mysql> show tables;
Empty set (0.00 sec)

Run the show tables command to check whether a data table exists in the database. The query result is blank.

Run the following command to create a data table. The table content is described above.

Copy codeThe Code is as follows:
Mysql> create table users (id int (2) not null primary key auto_increment, username varchar (40), password text, email text) default charset = utf8;
Query OK, 0 rows affected (0.12 sec)

The created data table name is users, which contains the preceding fields. You can use the following method to check the structure of the data table.

Copy codeThe Code is as follows:
Mysql> show tables;
+ ---------------------- +
| Tables_in_qiwsirtest |
+ ---------------------- +
| Users |
+ ---------------------- +
1 row in set (0.00 sec)

The query shows that there is already a table named users in the qiwsirtest database.

Copy codeThe Code is 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 users table:

Id: an id is automatically added for each added user.
Username: storage username, type: varchar (40)
Password: stores the user password in text format.
Email: stores users' mailboxes in text format.
Note: here, I have not injected every field and cannot set it as null. In real development, I may have to leave username and password empty.

This structure is the same as the expected structure, but this table does not have any data and is an empty table. You can query it to see:

Copy codeThe Code is as follows:
Mysql> select * from users;
Empty set (0.01 sec)

Currently, the table is empty. To use python to operate the data table later, you need to insert the vertex information to it. Just insert one entry.

Copy codeThe Code is 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 completed, and the next step is to use python.

Python database operations

To operate a database, you need to connect to it first. In the previous lecture, the official connection was passed. However, after that, you disabled the python interaction mode, so you have to re-connect it. This is also a disadvantage of the interactive mode. However, the operation here is intuitive, so I have to endure it for the moment. I will explain how to automatically complete it in the program later.

Copy codeThe Code is as follows:
>>> Import MySQLdb
>>> Conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "123123", db = "qiwsirtest", charset = "utf8 ")

When the connection is completed, a conn Instance Object of MySQLdb. connect () is established. What attributes does this object have?

Commit (): If the database table is modified, submit and save the current data. Of course, if this user has no permissions, nothing will happen.
Rollback (): If you have the permission, cancel the current operation. Otherwise, an error is returned.
Cursor ([cursorclass]): cursor. The following is a detailed description.
After the connection is successful, start the operation. Note: MySQLdb uses the cursor (pointer) cursor to operate the database, as shown in the following figure:

Copy codeThe Code is as follows:
>>> Cur = conn. cursor ()

Because the underlying layer of this module actually calls CAPI, you must first obtain the pointer to the database. This reminds us that when we operate the database, the pointer will move. If we move the pointer to the last entry of the database, we can't find anything. You can see the following example.

The following uses the method provided by cursor () to perform operations:

Execute Command
Receive results

Cursor command execution method:

Execute (query, args): executes a single SQL statement. Query is the SQL statement, and args is the list of parameter values. The returned value is the number of affected rows.
Executemany (query, args): executes a single SQL statement, but repeats the execution of parameters in the parameter list. The returned value is the number of affected rows.
For example, to insert a record in the data table users so that: username = "python", password = "123456", email = "python@gmail.com", do this:

Copy codeThe Code is as follows:
>>> Cur.exe cute ("insert into users (username, password, email) values (% s, % s, % s)", ("python", "123456 ", "python@gmail.com "))
1L

No error is reported, and a "1L" result is returned, indicating that a row of records has been successfully operated. Check the interaction mode of "mysql>:

Copy codeThe Code is as follows:
Mysql> select * from users;
+ ---- + ---------- + ------------------ +
| Id | username | password | email |
+ ---- + ---------- + ------------------ +
| 1 | qiwsir | 123123 | qiwsir@gmail.com |
+ ---- + ---------- + ------------------ +
1 row in set (0.00 sec)

Sorry, it's strange. Why didn't I see the added one? What's wrong? But no error is reported.

Here, please note that after you perform operations on the database through "cur.exe cute ()", no error is reported. It is completely correct, but data that is not equal to the value has been submitted to the database, "MySQLdb. A Property of connect: commit (), which submits the data, that is, the "cur.exe cute ()" operation. To submit the data, you must execute:

Copy codeThe Code is as follows:
>>> Conn. commit ()

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

Copy codeThe Code is 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. Indeed. This is like writing a text, writing the text to the text, not equal to the text has been reserved in the text file, must be executed "CTRL-S" to save. That is, when you use python to operate a database, you must run "commit ()" to save the executed results after executing various SQL statements with "execute ()", please note that this attribute is "MySQLdb. connect () "instance.

Try to insert multiple commands "executetasks (query, args )".

Copy codeThe Code is as follows:
>>> Cur.exe cute.pdf ("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 ()

Check the result in "mysql>:

Copy codeThe Code is 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)

Multiple records are successfully inserted. Note that in "executemany (query, args)", query is still an SQL statement, but args is a tuple at this time, and the elements in the tuple are also tuple, each tuple corresponds to the field list in the SQL statement. This statement is actually executed multiple times. However, the execution process is not shown to us.

You can insert more actions. Let's take a look at the next lecture.