Familiar with Python, using Python to query and update databases, and learning python

Familiar with Python, using Python to query and update databases, and learning python

Review the existing results: (1) connecting to the database; (2) Establishing a pointer; (3) Inserting a record through a pointer; (4) submitting a record to save the inserted result to the database. In the interactive mode, let's get started first and then let us know.

Copy codeThe Code is as follows:
>>># Import module
>>> Import MySQLdb

>>># Connect to the database
>>> Conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "123123", db = "qiwsirtest", port = 3036, charset = "utf8 ")

>>># Create a pointer
>>> Cur = conn. cursor ()

>>># Insert record
>>> Cur.exe cute ("insert into users (username, password, email) values (% s, % s, % s)", ("Old Qi", "9988 ", "qiwsir@gmail.com "))
1L

>>># Submit and save
>>> Conn. commit ()

If my audience looks like me, I am a little obsessive. I always think that I have to see the data to put my mind on it. Go to the database and check it out.

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 |
| 7 | old Qi | 9988 | qiwsir@gmail.com |
+ ---- + ---------- + ------------------ +
7 rows in set (0.00 sec)

The inserted record was also very impressive during the warm-up. However, I would like to remind you that when I set up this database and data table, we have set the character encoding to utf8. Therefore, the Chinese characters can be displayed in the query results. Otherwise, we will see a bunch of codes that you don't understand. If the visitor encounters a problem, do not panic. You only need to modify the character encoding. How to change it? Google. There are many websites.

The ending point is to learn about new things.

Query data

Based on the previous operations, if you want to query data from the database, you can also use pointers.

Copy codeThe Code is as follows:
>>> Cur.exe cute ("select * from users ")
7L

This indicates that seven records are queried from the users table. However, it seems unfriendly to me. I have found seven records, but where can I find them, then, seven records are listed. How can I display the query results of python here?

Previously, in Pointer instances, we had to use this method to implement the above idea:

Fetchall (self): receives all returned result rows.
Fetchmany (size = None): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.
Fetchone (): returns a result line.
Scroll (value, mode = 'relative '): Move the pointer to a row. if mode = 'relative ', the value bar is moved from the current row. If mode = 'absolute', the value bar is moved from the first row of the result set.
Follow these rules to try:

Copy codeThe Code is as follows:
>>> Cur.exe cute ("select * from users ")
7L
>>> Lines = cur. fetchall ()

At this point, nothing has been seen. In fact, the queried records (think of them as objects) have been assigned to the variable lines. If you want to display them, you need to use the loop statements you have learned.

Copy codeThe Code is as follows:
>>> For line in lines:
... Print line
...
(1L, u'qiwsir ', u'000000', u'qiwsir @ gmail.com ')
(2L, u 'python', u '000000', u 'python @ gmail.com ')
(3L, u 'Google ', u '000000', u 'G @ gmail.com ')
(4L, u'facebook ', u'000000', u'f @ face. Book ')
(5L, u 'github ', u '123', u 'git @ hub.com ')
(6L, u 'docker', u '000000', u'doc @ ker.com ')
(7L, U' \ u8001 \ u9f50 ', u'000000', u'qiwsir @ gmail.com ')

Good. It is displayed one by one. Note that U' \ u8001 \ u95f5 'in Article 7 is a Chinese character, but because my shell cannot be displayed, you don't have to worry about it.

I just want to find the first one, OK? Of course! See the following:

Copy codeThe Code is as follows:
>>> Cur.exe cute ("select * from users where id = 1 ")
1L
>>> Line_first = cur. fetchone () # Only one record is returned.
>>> Print line_first
(1L, u'qiwsir ', u'000000', u'qiwsir @ gmail.com ')

To gain an in-depth understanding of the above process, do the following experiment:

Copy codeThe Code is as follows:
>>> Cur.exe cute ("select * from users ")
7L
>>> Print cur. fetchall ()
(1L, u'qiwsir ', u'000000', u'qiwsir @ gmail.com'), (2L, u'python', u'000000 ', u'python @ gmail.com '), (3L, u 'Google', u '000000', u 'G @ gmail.com '), (4L, u 'Facebook ', u'000000', u'f @ face. book '), (5L, u 'github', u '100', u 'git @ hub.com '), (6L, u 'docker', u '100 ', u'doc @ ker.com '), (7L, U' \ u8001 \ u9f50', u'000000', u'qiwsir @ gmail.com '))

Originally, things that are queried from the database using cur.exe cute () are "saved somewhere that cur can find". To find these things that are saved, use cur. fetchall () (or fechone), and then exist as an object. From the above experiment, we found that the stored object is a tuple, and each element in it is a tuple. Therefore, you can use the for loop to get it out one by one.

Does an official understand its connotation?

Next, let's see what's amazing.

Next, print the operation again.

Copy codeThe Code is as follows:
>>> Print cur. fetchall ()
()

Dizzy! Why is it empty? Isn't it because the object already exists in the memory? Is the object in the memory valid once?

Don't worry.

An object found by pointer has a feature in reading, that is, the pointer will move. After the print cur. fetchall () operation is performed for the first time, the pointer is moved from the first to the last one because all the data is printed. When print ends, the pointer is behind the last one. If you print it again, it will be empty, and there will be nothing behind the last one.

The following is an experiment to test what is said above:

Copy codeThe Code is as follows:
>>> Cur.exe cute ('select * from users ')
7L
>>> Print cur. fetchone ()
(1L, u'qiwsir ', u'000000', u'qiwsir @ gmail.com ')
>>> Print cur. fetchone ()
(2L, u 'python', u '000000', u 'python @ gmail.com ')
>>> Print cur. fetchone ()
(3L, u 'Google ', u '000000', u 'G @ gmail.com ')

This time I did not print them all at once, but one at a time. The viewer can see from the result that the pointer was moving down one by one. Note: In this experiment, I re-run the query statement.

So, since the pointer moves when operating the objects stored in the memory, can you move the pointer up or to the specified position? This is the scroll ()

Copy codeThe Code is as follows:
>>> Cur. scroll (1)
>>> Print cur. fetchone ()
(5L, u 'github ', u '123', u 'git @ hub.com ')
>>> Cur. scroll (-2)
>>> Print cur. fetchone ()
(4L, u'facebook ', u'000000', u'f @ face. Book ')

Sure enough, this function can move the pointer, but please observe carefully. The above method is to move the pointer up or down relative to the current position. That is:

Cur. scroll (n), or, cur. scroll (n, "relative"): indicates moving up or down relative to the current position. n is a positive number, indicating downward (forward), n is a negative number, and upward (backward)

There is also a way to achieve "absolute" move, not "relative" Move: Add a parameter "absolute"

Note that in python, the sequence of sequential objects starts from 0.

Copy codeThe Code is as follows:
>>> Cur. scroll (2, "absolute") # Return to the sequence number 2, but point to the third
>>> Print cur. fetchone () # print, which is indeed
(3L, u 'Google ', u '000000', u 'G @ gmail.com ')

>>> Cur. scroll (1, "absolute ")
>>> Print cur. fetchone ()
(2L, u 'python', u '000000', u 'python @ gmail.com ')

>>> Cur. scroll (0, "absolute") # Return to the number 0, that is, pointing to the first entry of the tuple.
>>> Print cur. fetchone ()
(1L, u'qiwsir ', u'000000', u'qiwsir @ gmail.com ')

Now, you are familiar with cur. fetchall () and cur. fetchone () and cur. several methods of scroll (), and another operation above, that is, the pointer is at the position where the serial number is 1, pointing to the second record of tuple

Copy codeThe Code is as follows:
>>> Cur. fetchtasks (3)
(2L, u'python', u'000000', u'python @ gmail.com '), (3L, u'google', u'000000 ', u 'G @ gmail.com '), (4L, u 'Facebook', u '000000', u 'f @ face. book '))

The above operation is to start from the current position (the Pointer Points to the position where the tuple serial number is 1, that is, the second record), including the current position, and list three records down.

Reading data seems awkward. I think it makes sense. What do you think?

However, python can always be used for our consideration. Its pointer provides a parameter to convert the read data into a dictionary. This provides another reading method.

Copy codeThe Code is as follows:
>>> Cur = conn. cursor (cursorclass = MySQLdb. cursors. DictCursor)
>>> Cur.exe cute ("select * from users ")
7L
>>> Cur. fetchall ()
({'Username': u'qiwsir ', 'Password': u'000000', 'id': 1L, 'email': u'qiwsir @ gmail.com '}, {'username': u'mypython', 'Password': u'000000', 'id': 2L, 'email ': u'python @ gmail.com '}, {'username': u'google ', 'Password': u'000000', 'id': 3L, 'email': U' g @ gmail.com '}, {'username': u'facebook ', 'Password': u'000000', 'id': 4L, 'email': u'f @ face. book '}, {'username': u'github', 'Password': u'000000', 'id': 5L, 'email ': u'git @ hub.com '}, {'username': u'docker', 'Password': u'000000', 'id': 6L, 'email ': u'doc @ ker.com '}, {'username': U' \ u8001 \ u9f50', 'Password': u'000000', 'id': 7L, 'email ': u'qiwsir @ gmail.com '})

In this way, the elements in the tuples are a dictionary. You can operate on this object as follows:

Copy codeThe Code is as follows:
>>> Cur. scroll (0, "absolute ")
>>> For line in cur. fetchall ():
... Print line ["username"]
...
Qiwsir
Mypython
Google
Facebook
Github
Docker
Laoqi

The "key-value" is read Based on the characteristics of the dictionary object ".

Update Data

After the previous operations, this is relatively simple, but you need to note that if the update is complete, like inserting data, you need to submit () to save.

Copy codeThe Code is as follows:
>>> Cur.exe cute ("update users set username = % s where id = 2", ("mypython "))
1L
>>> Cur.exe cute ("select * from users where id = 2 ")
1L
>>> Cur. fetchone ()
(2L, u 'mypython', u '000000', u 'python @ gmail.com ')

The operation shows that the second username in the database has been changed to mypython, and the update statement is used.

However, to update the database, run the following command:

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

This is the end of the event.