Using Python to manipulate databases (1)

We connected to the database in the last lecture. In the case of a database, you operate on it after a connection. However, at present, the data named qiwsirtest is just an empty shelf, there is nothing to operate, to operate it, you must set up a "table" in it, what is the database table? Here is a brief excerpt from wikipedia's explanation of database tables. To understand this in detail, you need to look for tutorials and books on databases.

In a relational database, a database table is a collection of two-dimensional arrays that represent and store relationships between data objects. It consists of vertical columns and horizontal rows, such as a table named authors with author information, where each column contains a particular type of information about all authors, such as "last name," and each row contains all information about a particular author: last name, first name, address, and so on.
For a particular database table, the number of columns is generally fixed in advance, and each column can be identified by the column name. The number of rows can change at any time, dynamically, and each row can usually be identified based on data in a particular (or several) column, called a candidate key.
I plan to set up a table to store user name, user password and user mailbox in qiwsirtest. Its structure is shown as follows in a two-dimensional table:

The username   The password   E-mail.
qiwsir   123123   (link: #)

In particular, in order to simplify the details and highlight the point, the password is not encrypted, direct plaintext storage, although this way is very insecure. But, have a lot of website still do so, the purpose that does so is more abhorrent. Just let me be here, just one damn time.

Create database tables and insert data

To create this table in the database, you need to go to mysql > Operation in interactive mode. The reason is, if qiwsirtest does not have a variety of database tables like furniture in this room, there is nothing good to operate even into the room, so you need to go to mysql first > Put furniture in the room in the mode.

Enter database interaction mode:

qw@qw-Latitude-E4300:~$ mysql -u root -p

Enter password: 

Call the established database: qiwsirtest

mysql> use qiwsirtest;

Database changed

mysql> show tables;

Empty set (0.00 sec)

Use the show tables command to show if there are any tables in the database. The query result is empty.

The following commands are used to create a data table whose contents are described above.

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 name of the data table created is: users, which contains the above fields. You can take a look at the structure of the data table in the following way.

mysql> show tables;

+----------------------+

| Tables_in_qiwsirtest |

+----------------------+

| users                |

+----------------------+

1 row in set (0.00 sec)

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

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)

Show the structure of table users:

Id: every time a user is added, the id number is automatically added.
Username: stores the username of type varchar(40)
Password: stores the user's password, of type text
Email: the type of email that stores the user's email
Note: in this case, I didn't inject each field and don't set it to be null, etc. In real development, you might have to make username and password not null.

This structure is the same as the expected structure above, except that there is no data in this table, which is an empty table. Check it out:

mysql> select * from users;

Empty set (0.01 sec)

The table is currently empty, and in order to be able to manipulate the table later in python, you need to insert point information into it, just one.

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, in mysql > Now that you've done the work in, it's time to do it in python.

Python manipulation database

To operate on the database, you need to connect to it first. You connected last time, but then you turned off the python interaction mode, so you need to reconnect. This is also a drawback of the interaction pattern. But it's intuitive here, so bear with it for a moment, and then we'll show you how to do it automatically in the program.

>>> import MySQLdb

>>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123123",db="qiwsirtest",charset="utf8")

The process of completing the connection is to create an instance object, conn, of mysqldb.connect (). What properties does this object have?

Commit (): if the database table is modified, commit saves the current data. Of course, if the user doesn't have permission, nothing happens.
Rollback (): if you have permission, cancel the current operation or report an error.
Cursor ([cursorclass]): cursor pointer. Here's more.
After a successful connection, the operation begins. Note: MySQLdb operates with a cursor on the database, and that's it:

>>> cur = conn.cursor()

Because the underlying module is actually calling CAPI, you need to get the current pointer to the database first. This also reminds us that when we operate on the database, the pointer will move. If we move to the last item in the database, we will look it up again. Just look at the following example.

The following operations are performed using methods provided with cursor(), mainly:

Execute the command
Receiving results

Cursor executes the command method:

Execute (query, args): execute a single SQL statement. Query is the SQL statement itself, and args is the list of parameter values. The return value after execution is the number of affected rows.
Executemany (query, args): executes a single SQL statement, but repeats the arguments in the argument list, returning the number of affected rows
For example, to insert a record in the table users, make :username="python",password="123456",email=" (link: #)"

>>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","python@gmail.com"))

1L

No error was reported, and a "1L" result was returned, indicating that one line recorded a successful operation. Might as well use "mysql > "Interactive mode check:

mysql> select * from users;

+----+----------+----------+------------------+

| id | username | password | email            |

+----+----------+----------+------------------+

|  1 | qiwsir   | 123123   | qiwsir@gmail.com |

+----+----------+----------+------------------+

1 row in set (0.00 sec)

Gee, that's strange. Why don't you see the extra one? What went wrong? But there was no error.

Here, a special reader, please note that by "cur. The execute ()" for database operation, no error, completely correct, but is not equal to the data already submitted to the database, also must use "MySQLdb. Connect" of an attribute: the commit (), the data submitted, namely the "cur. An execute () operation, to the data submitted, shall be carried out:

>>> conn.commit()

In the "mysql > Run "select * from users" in "select * from users"

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. Just like writing text, writing to text does not mean that the text is already in the text file. You must execute "ctrl-s" to save it. After executing various SQL statements as "execute()" when working with the database through python, you must run "commit()" to save the effect that has been executed, and remind that this property is an instance of "mysqldb.connect ()".

Try again the command "executemany(query,args)" that inserts multiple Numbers.

>>> 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 the "mysql > "Look inside:

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 were successfully inserted. Note, in particular, that in "executemany(query,args)", the query is still an SQL statement, but the args is then a tuple, and the elements in that tuple are also tuples, each of which corresponds to a list of fields in the SQL statement. This phrase has actually been executed many times. It's just that the execution is not shown to us.

I've inserted it, and then I can do more. Let's move on to the next lecture.