Use Python queries to update a database

Review the results :(1) connect to the database; (2) establish pointer; (3) insert records through Pointers; (4) commit to save the insert results to the database. In interactive mode, learn the old before you learn the new.

>>> # The import module 

>>> import MySQLdb
>>> # Connect to database 

>>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123123",db="qiwsirtest",port=3036,charset="utf8")
>>> # Establish a pointer 

>>> cur = conn.cursor()
>>> # Insert records 

>>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",(" Old together ","9988","qiwsir@gmail.com"))

1L
>>> # Submit to save 

>>> conn.commit()

If the judge is like me, a little obsessive-compulsive, always think that I have to see the data, then let the heart ah. So let's go into the database and see.

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 together      | 9988     | qiwsir@gmail.com |

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

    7 rows in set (0.00 sec)

When I was just passing by, the inserted record also stood out clearly. But here is a special reminder, I set up the database and data table in front of the time, has set the character encoding as utf8, so, now see the query results, can display Chinese characters. Otherwise, you will see a pile of code you do not understand. If the viewer meets, please do not panic, just need to modify the character encoding. How to change? Please Google. It's a lot online.

So the end, the beginning of new knowledge.

Query data

On the basis of the previous operation, if you want to query data from the database, of course, you can also use the pointer to operate.

>>> cur.execute("select * from users")    

7L

This indicates that seven records were retrieved from the users table summary. However, it seems a bit unfriendly to tell me that 7 records were found out, but where is it looking in front of 'mysql > 'when you run the query command, you list seven records. How do you display python's query results here?

Originally, in the pointer instance, this method is needed to realize the above idea:

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

>>> cur.execute("select * from users")    

7L

>>> lines = cur.fetchall()

At this point, you haven't seen anything, but you've already assigned the queried records (think of them as objects) to the variable lines. If you want to display them, you need to use the loop statement you learned before.

>>> 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'u8001u9f50', u'9988', u'qiwsir@gmail.com')

Very good. And so it was. Note that u'\u8001\u95f5' in article 7 is a Chinese character, but since my shell cannot display it, don't panic and don't bother.

I just want the first one, okay? Sure! Take a look at the following:

>>> cur.execute("select * from users where id=1")

1L

>>> line_first = cur.fetchone()     # Only one return 

>>> print line_first

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

To further understand the above process, do the following experiments:

>>> 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'u8001u9f50', u'9988', u'qiwsir@gmail.com'))

It turns out that a curd. execute() query from a database is "stored somewhere that can be found by a cur." to find it, cur. Fetchall () (or fechone, etc.) is used and, once found, exists as an object. From the above experiment, we found that the object to be saved is a tuple, and every element in it is a tuple. So you can take them out one by one using the for loop.

Does the judge understand its connotation?

And then there's the magic.

So I'm going to go ahead and print it again

>>> print cur.fetchall()

()

Dizzy!!!! How is what empty? Isn't it already in memory as an object? Is the object in memory valid at once?

Don't worry.

One of the things that happens when an object is read from a pointer is that the pointer moves. After the first operation of print cur. Fetchall (), the pointer moves from the first to the last, because all is printed. When print ends, the pointer is already after the last print. And then if I print it again, it's empty, there's nothing after the last one.

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

>>> 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 of them, but one at a time, the viewer can see from the results, and sure enough the pointer is moving down one by one. Notice that I rerun the query in this case.

So, since the pointer moves while manipulating an object stored in memory, can you move the pointer up, or to the specified location? That's the scroll.

>>> 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, the function can move the pointer, but watch carefully, and the way it works is to move the pointer up or down relative to the current position. That is:

Curd. scroll(n), or curd. scroll(n,"relative") : means to move up or down relative to the current position, n is positive, means down (forward), n is negative, means up (backward)

There is another way to achieve "absolute" movement, not "relative" movement: add a parameter "absolute"

It is important to note that in python, the sequence of objects starts at 0.

>>> cur.scroll(2,"absolute")    # So let's go back to the number 2, But it points to number three 

>>> print cur.fetchone()        # Print, 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")    # So let's go back to the number 0, The point to tuple The first article 

>>> print cur.fetchone()

(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

So by now, we're familiar with cur. Fetchall () and cur. Fetchone () and curd. scroll(), and then there's another one, which takes this up here, which is that the pointer, at the number 1, points to the second part of the tuple

>>> 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 to start from the current position (pointer to the tuple's number 1, the second record), including the current position, and list down three records.

Read the data, it seems a bit verbose ah. After careful consideration, it still makes sense. What do you think?

However, python has always been good for us. Its pointer provides an argument that turns the data it reads into a dictionary, providing another way to read it.

>>> 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, 'email': 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'u8001u9f50', 'password': u'9988', 'id': 7L, 'email': u'qiwsir@gmail.com'})    

In this way, the elements inside the tuple are a dictionary. This object can be manipulated by:

>>> cur.scroll(0,"absolute")

>>> for line in cur.fetchall():

...     print line["username"]

... 

qiwsir

mypython

google

facebook

github

docker

 Old together 

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

Update the data

After the previous operation, this is relatively simple, but it is important to note that if the update is complete, as with the inserted data, a commit() is required to commit the save.

>>> 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')

As you can see from the operation, the second username in the database has been changed to mypython, using the update statement.

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

>>> conn.commit()

That's the end of the story.