With old Ziko python using Python queries to update the database

Review the existing results: (1) Connect the database, (2) Set up the pointer, (3) Insert the record through the pointer, and (4) commit to save the insert result to the database. In the interactive mode, the first temperature, and then know the new.

The code is as follows:

>>> #导入模块
>>> Import MySQLdb

>>> #连接数据库
>>> conn = MySQLdb.connect (host= "localhost", user= "root", passwd= "123123", db= "Qiwsirtest", port=3036, charset= "UTF8")

>>> #建立指针
>>> cur = conn.cursor ()

>>> #插入记录
>>> Cur.execute ("INSERT into users (Username,password,email) VALUES (%s,%s,%s)", "Old Qi", "9988", " Qiwsir@gmail.com "))
1L

>>> #提交保存
>>> Conn.commit ()

If crossing and I, a little obsessive-compulsive disorder, always think I have to see the data, just put heart. Then go into the database and see.

The 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 | Lao Qi | 9988 | qiwsir@gmail.com |
+----+----------+----------+------------------+
7 Rows in Set (0.00 sec)

Just now, when the temperature, the record inserted is also impressively in the eye. But here is a special reminder crossing, I built this database and data table in front of the time, has been set up the character encoding for UTF8, so, in the query results now seen can display Chinese characters. Otherwise, you will see a bunch of what you don't understand. If crossing encounters, please do not panic, only need to modify the character encoding. How to change? Please google. Online a lot.

The end of the temperature, began to know the new.

Querying data

On the basis of the previous operation, if you want to query the data from the database, you can of course use pointers to manipulate.

The code is as follows:

>>> Cur.execute ("SELECT * from users")
7L

This indicates that 7 records were collected from the Users table summary query. But, this seems a little unfriendly, tell me 7 records to find out, but where, look at the front of the ' mysql> ' under the operation of the query command, a bit of the 7 records listed. How do i show the results of Python's query here?

Originally, in the pointer instance, in this way, in order to achieve the above idea:

Fetchall (self): receives all the returned result rows.
Fetchmany (Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned.
Fetchone (): Returns a result row.
Scroll (value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if mode= ' absolute ', Represents the move value bar from the first row of the result set.
Following these rules, try:

The code is as follows:

>>> Cur.execute ("SELECT * from users")
7L
>>> lines = Cur.fetchall ()

Here, there is nothing to see, in fact, the query to the records (they are regarded as objects) assigned to the variable lines. If you want to show them, you need to use the loop that you've learned.

The code is as follows:

>>> for line in lines:
.. Print Line
...
(1L, U ' Qiwsir ', U ' 123123 ', U ' qiwsir@gmail.com ')
(2L, U ' python ', U ' 123456 ', U ' python@gmail.com ')
(3L, U ' Google ', U ' 111222 ', U ' g@gmail.com ')
(4L, U ' Facebook ', U ' 222333 ', U ' f@face.book ')
(5L, U ' github ', U ' 333444 ', U ' git@hub.com ')
(6L, U ' docker ', U ' 444555 ', U ' doc@ker.com ')
(7L, U ' \u8001\u9f50 ', U ' 9988 ', U ' qiwsir@gmail.com ')

Very good. Sure enough, it came out by article. Yours faithfully note that the seventh in the U ' \u8001\u95f5 ', here is the Chinese character, but because my shell can not display it, do not panic, do not have to talk about it.

Just want to find out the first one, okay? Of course! Look at the following:

The code is as follows:

>>> Cur.execute ("SELECT * from Users where id=1")
1L
>>> Line_first = Cur.fetchone () #只返回一条
>>> Print Line_first
(1L, U ' Qiwsir ', U ' 123123 ', U ' qiwsir@gmail.com ')

In order to understand the above process, do the following experiment:

The code is as follows:

>>> Cur.execute ("SELECT * from users")
7L
>>> Print Cur.fetchall ()
((1L, U ' Qiwsir ', U ' 123123 ', U ' qiwsir@gmail.com '), (2L, U ' python ', U ' 123456 ', U ' python@gmail.com '), (3L, U ' Google ', U ' 111222 ', U ' g@gmail.com '), (4L, U ' Facebook ', U ' 222333 ', U ' F@face.book '), (5L, U ' github ', U ' 333444 ', U ' git@hub.com '), ( 6L, U ' docker ', U ' 444555 ', U ' doc@ker.com '), (7L, U ' \u8001\u9f50 ', U ' 9988 ', U ' qiwsir@gmail.com '))

Originally, with Cur.execute () from the database query out of things, was "saved in Cur can find a place", to find out these saved things, need to use Cur.fetchall () (or fechone, etc.), and find out, as objects exist. From the above experiment, it is found that the object being saved is a tuple, and each element in it is a tuple of one. Therefore, you can use a for loop to take it out.

Does crossing understand its connotation?

And then look, there's something magical about it.

Then the above operation, and then print again

The code is as follows:

>>> Print Cur.fetchall ()
()

It's dizzy! Why what is empty? Not that the object already exists in memory? Is this object in memory valid at once?

Don't worry.

The object that is found by the pointer, when read, has a feature that the pointer will move. After the first operation of print Cur.fetchall (), since it is all printed, the pointer moves from the first to the last. When print finishes, the pointer is already behind the last bar. Next, if you print again, it's empty, and there's nothing behind the last one.

The following is also an experiment, to test the above said:

The code is as follows:

>>> cur.execute (' SELECT * from users ')
7L
>>> Print Cur.fetchone ()
(1L, U ' Qiwsir ', U ' 123123 ', U ' qiwsir@gmail.com ')
>>> Print Cur.fetchone ()
(2L, U ' python ', U ' 123456 ', U ' python@gmail.com ')
>>> Print Cur.fetchone ()
(3L, U ' Google ', U ' 111222 ', U ' g@gmail.com ')

This time I did not print out all at once, but one print at a time, crossing can be seen from the results, sure enough, the pointer is moving downward in a single line. Note that in this experiment, I rerun the query statement.

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

The code is as follows:

>>> Cur.scroll (1)
>>> Print Cur.fetchone ()
(5L, U ' github ', U ' 333444 ', U ' git@hub.com ')
>>> Cur.scroll (-2)
>>> Print Cur.fetchone ()
(4L, U ' Facebook ', U ' 222333 ', U ' f@face.book ')

Sure enough, this function can move the pointer, but look closely, the way it is to move the pointer up or down relative to the current position. That

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

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

In particular, crossing note that in Python, sequence objects are ordered from 0.

The code is as follows:

>>> Cur.scroll (2, "absolute") #回到序号是2 but pointed to the third article
>>> print Cur.fetchone () #打印, sure enough.
(3L, U ' Google ', U ' 111222 ', U ' g@gmail.com ')

>>> Cur.scroll (1, "absolute")
>>> Print Cur.fetchone ()
(2L, U ' python ', U ' 123456 ', U ' python@gmail.com ')

>>> cur.scroll (0, "absolute") #回到序号是0, which points to the first of the tuple
>>> Print Cur.fetchone ()
(1L, U ' Qiwsir ', U ' 123123 ', U ' qiwsir@gmail.com ')

At this point, we are already familiar with Cur.fetchall () and Cur.fetchone () and Cur.scroll () several methods, and another, the next operation, that is, the pointer in the ordinal is 1 of the position, pointing to the second of the tuple

The code is as follows:

>>> Cur.fetchmany (3)
((2L, U ' python ', U ' 123456 ', U ' python@gmail.com '), (3L, U ' Google ', U ' 111222 ', U ' g@gmail.com '), (4L, U ' Facebook ', U ' 222333 ', U ' F@face.book '))

The above operation, is implemented from the current position (the pointer to a tuple ordinal of 1, that is, the second record) to start, with the current position, down the list of 3 records.

Reading data, it seems a bit wordy ah. It makes sense to ponder. What do you think?

However, Python can always be of interest to us, and its pointer provides a parameter that allows the data to be read into a dictionary form, which provides another way to read.

The code is as follows:

>>> cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor)
>>> Cur.execute ("SELECT * from users")
7L
>>> Cur.fetchall ()
({' username ': U ' qiwsir ', ' Password ': U ' 123123 ', ' id ': 1L, ' email ': U ' qiwsir@gmail.com '}, {' username ': U ' mypython ', ' Password ': U ' 123456 ', ' id ': 2L, ' email ': U ' python@gmail.com '}, {' username ': U ' google ', ' password ': U ' 111222 ', ' id ': 3L, ' E Mail ': U ' g@gmail.com '}, {' username ': U ' Facebook ', ' Password ': U ' 222333 ', ' id ': 4L, ' email ': U ' f@face.book '}, {' username ' : U ' github ', ' Password ': U ' 333444 ', ' id ': 5L, ' email ': U ' git@hub.com '}, {' username ': U ' docker ', ' Password ': U ' 444555 ', ' ID ': 6L, ' email ': U ' doc@ker.com '}, {' username ': U ' \u8001\u9f50 ', ' Password ': U ' 9988 ', ' id ': 7L, ' email ': U ' qiwsir@gmail.com '})

In this way, the element in the tuple is a dictionary. You can manipulate this object in this way:

The code is as follows:

>>> cur.scroll (0, "absolute")
>>> for line in Cur.fetchall ():
... print line["username"]
...
Qiwsir
Mypython
Google
Facebook
GitHub
Docker
Lao Qi

The key-value is read according to the characteristics of the Dictionary object.

Update data

This is easier after the previous operation, but it is necessary to note that if the update is complete and the data is inserted, commit () is required to commit the save.

The code is as follows:

>>> cur.execute ("Update users set username=%s where id=2", ("Mypython"))
1L
>>> Cur.execute ("SELECT * from Users where id=2")
1L
>>> Cur.fetchone ()
(2L, U ' Mypython ', U ' 123456 ', U ' python@gmail.com ')

From the operation, the second user name in the database has been modified to Mypython, with the UPDATE statement.

However, to actually implement the update in the database, you also need to run:

The code is as follows:

>>> Conn.commit ()

This is a big deal.

