Python query update MySQL database (add to check)

First, we take the following steps: (1) connecting the database, (2) establishing the pointer, (3) inserting the record through the pointer, and (4) submitting the insert result to the database.

>>> #导入模块
>>> 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 "," <a href= "mailto:qiwsir@gmail.com" target= "_blank" > Qiwsir@gmail.com</a> "))
1L
>>> #提交保存
>>> conn.commit ()

Enter the database first to see if the data was successfully inserted


Mysql> select * from users;

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

| ID | Username | password | email |

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

| 1 | Qiwsir | 123123 | <a href= "mailto:qiwsir@gmail.com" target= "_blank" >qiwsir@gmail.com</a> |

| 2 | Python | 123456 | <a href= "mailto:python@gmail.com" target= "_blank" >python@gmail.com</a> |

| 3 | Google | 111222 | <a href= "mailto:g@gmail.com" target= "_blank" >g@gmail.com</a> |

| 4 | Facebook | 222333 | <a href= "Mailto:f@face.book" target= "_blank" >f@face.book</a> |

| 5 | GitHub | 333444 | <a href= "mailto:git@hub.com" target= "_blank" >git@hub.com</a> |

| 6 | Docker | 444555 | <a href= "mailto:doc@ker.com" target= "_blank" >doc@ker.com</a> |

| 9 2 Old Qi | 9988 | <a href= "mailto:qiwsir@gmail.com" target= "_blank" >qiwsir@gmail.com</a> |

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

7 Rows in Set (0.00 sec)


We can see that 7 records exist. But here special reminder, I set up in front of this database and data table, has been set the character encoding for UTF8, so, in the query results now see, you can display Chinese characters. Otherwise, you will see a bunch of what you don't understand. If you encounter, please do not panic, only need to modify the character encoding can be. How to change? Please google. A lot of online.


Querying 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 7 records were made from the Users Table rollup query. However, this seems a bit unfriendly, tell me 7 records found out, but where it, look at the "mysql>" under the operation of the query command, then the 7 records listed. How do you show Python's query results here?

It turns out that in the pointer instance, you have to use this method to achieve this idea:

Fetchall (self): receives all returned result rows.

Fetchmany (Size=none): Receives the size bar to return the 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 ', it means moving the value bar from the current row, if mode= ' absolute ', Indicates that the value bar is moved from the first row of the result set.

Following these rules, try:

>>> Cur.execute ("SELECT * from users")
7l>>> lines = Cur.fetchall () <br><br>

Here, have not seen what, in fact, has been the query to the records (see them as objects) assigned to the variable lines. If you want to show them out, you need to use the circular statements that you have learned.

>>> for line in lines:      ...      print line      ...       (1l, u ' Qiwsir ',  u ' 123123 ',  <a href= "Mailto:u ' qiwsir@gmail.com '" " target=" _blank ">u ' qiwsir@gmail.com ' </a>)       (2l, u ' python ',  u ' 123456 ',  <a href= ' mailto:u ' python@gmail.com ')  target= "_blank" >u ' python@gmail.com ' </a>)       (3l, u ' Google ',  U ' 111222 ',  <a href= "Mailto:u ' g@gmail.com '"  target= "_blank" ">u ' g@gmail.com ' </a>)       (4l, u ' Facebook ',  u ' 222333 ',  <a href= ' mailto:u ' F@face.book ')  target= "_blank" >u ' F@face.book ' </a>)       (5l, u ' GitHub ',  u ' 333444 ',
 <a href= "Mailto:u ' git@hub.com '"  target= "_blank" >u ' git@hub.com ' </a>)       (6L,&NBSp;u ' Docker ',  u ' 444555 ',  <a href= ' mailto:u ' doc@ker.com ' " target=" _blank ">u" doc@ Ker.com ' </a>)       (7l, u ' \u8001\u9f50 ',  u ' 9988 ',  <a href= " Mailto:u ' qiwsir@gmail.com ' " target=" _blank ">u ' qiwsir@gmail.com ' </a>)

Very good. Sure enough, it's a show. Note that in the seventh, U ' \u8001\u95f5 ', here is Chinese characters, just because my shell can not show it, do not panic, do not have to talk about it.

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

>>> Cur.execute ("SELECT * from Users where id=1")
1L
>>> line_first = cur.fetc Hone () #只返回一条
>>> print Line_first
(1L, U ' Qiwsir ', U ' 123123 ', <a href= ' mailto:u ' Qiwsir@gmail.com ' "target=" _blank ">u ' qiwsir@gmail.com ' </a>)

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

>>> cur.execute ("Select * from users")       7l      >>> print cur.fetchall ()       (1l, u ' Qiwsir ',  U ' 123123 ',  <a href= "Mailto:u ' qiwsir@gmail.com" " target=" _blank ">u" "qiwsir@gmail.com" </a >),  (2l, u ' python ',  u ' 123456 ',  <a href= ' mailto:u ' python@gmail.com ' " target") = "_blank" >u ' python@gmail.com ' </a>),  (3l, u ' Google ',  u ' 111222 ',  <a href= ') Mailto:u ' g@gmail.com ' " target=" _blank ">u ' g@gmail.com ' </a>),  (4l, u ' Facebook ',  U ' 222333 ',  <a href= "Mailto:u ' F@face.book '"  target= "_blank" >u ' ' F@face.book ' </a>),   (5l, u ' GitHub ',  u ' 333444 ',  <a href= ' mailto:u ' git@hub.com ' " target=" _blank " >u ' git@hub.com ' </a>),  (6l, u ' Docker ',  u ' 444555 ',  <a href= ' mailto:u ' doc@ Ker.com ' " target=" _Blank ">u ' doc@ker.com ' </a>",  (7l, u ' \u8001\u9f50 ',  u ' 9988 ',  <a href= ') Mailto:u ' qiwsir@gmail.com ' " target=" _blank ">u ' qiwsir@gmail.com ' </a>)"

Originally, something that was queried from the database with Cur.execute () was "saved somewhere in the cur", to find out what was saved, needed to use Cur.fetchall () (or fechone, etc.), and to find out, As objects exist. From the above experiment, it is found that the object being saved is a tuple, each element inside is a tuple of one. Therefore, the For loop can be taken out one by one. Does the

Reader understand its meaning?

Then look, there's magic.

and then print it again

>>> print cur.fetchall ()  
 
()  

Dizzy! What is empty? Does it mean that the object already exists in memory? Is this object in memory a valid one?

Don't worry.

The object that is found by the pointer, when read, has a feature that the pointer moves. After the first operation of print Cur.fetchall (), the pointer moves from the first to the last one because it prints everything. When print is finished, the pointer is behind the last one. Then if you print again, it's empty, and there's nothing behind the last one.

to experiment with the following, verify that the above:

>>> cur.execute (' select * from users ')       7l      >>> print cur.fetchone ()         (1l, u ' Qiwsir ',  u ' 123123 ',  <a href= ' mailto:u ' qiwsir@gmail.com ' "  target= "_blank" >u ' qiwsir@gmail.com ' </a>)       >>> print  Cur.fetchone ()       (2l, u ' python ',  u ' 123456 ',  <a href= "mailto: U ' python@gmail.com ' " target=" _blank ">u ' python@gmail.com ' </a>)       >> > print cur.fetchone ()       (3l, u ' Google ',  u ' 111222 ',  <a  href= "Mailto:u ' g@gmail.com '"  target= "_blank" >u ' g@gmail.com ' </a>) 

This time I print out all at once, but one at a time, the reader can be seen from the results, and sure enough that the pointer is moving down a line. Notice that in this experiment, I rerun the query statement.

So, now that the pointer moves when you're manipulating an object stored in memory, can you move the pointer up or move to the specified position? This is the scroll ()

>>> cur.scroll (1)
>>> print cur.fetchone ()
(5L, U ' github ', U ' 333444 ', <a HR  ef= "Mailto:u ' git@hub.com '" target= "_blank" >u ' git@hub.com ' </a>)
>>> Cur.scroll (-2)  
>>> Print cur.fetchone (
4L, u ' Facebook ', U ' 222333 ', <a href= "Mailto:u ' F@face.book '") target= "_blank" >u ' f@face.book ' </a>)

Sure enough, this function is able to move the pointer, but carefully observe the way it moves the pointer up or down relative to the current position. That is:
Cur.scroll (n), or, Cur.scroll (n, "relative"): means to move up or down relative to the current position, n is a positive number, to indicate downward (forward), N to a negative number, to indicate up (backwards)

There is another way, You can achieve "absolute" movement, not "relative" movement: Add an argument to "absolute"

specifically remind reader note that in Python, sequence objects are ordered from 0.

>>> cur.scroll (2, "absolute")      #回到序号是2, but point to article three       >
>> print cur.fetchone ()          #打印, sure enough       (3l, u ' Google ',  u ' 111222 ',  <a href= ' mailto:u ' g@gmail.com ' " target=" _ Blank ">u ' g@gmail.com ' </a>)    >>> cur.scroll (1," absolute ")        >>> print cur.fetchone ()       (2l, u ' python ',  u ' 123456 '),  <a href= "Mailto:u ' python@gmail.com '" " target=" _blank ">u ' python@gmail.com ' </a>)
   >>> cur.scroll (0, "absolute")      #回到序号是0, which points to tuple's first       >>> print cur.fetchone ()       (1l, u ' Qiwsir ',  U ' 123123 ',  <a href= "Mailto:u ' qiwsir@gmail.com" " target=" _blank ">u" "qiwsir@gmail.com" </a >)

At this point, you are familiar with the Cur.fetchall () and Cur.fetchone () and Cur.scroll () methods, as well as the other one, which is where the pointer is 1 in the ordinal. Point to Tuple's second


>>> cur.fetchmany (3)  
 
(2L, u ' python, u ' 123456 ', <a href= " Mailto:u ' python@gmail.com ' "target=" _blank ">u ' python@gmail.com ' </a>), (3L, U ' Google ', U ' 111222 ', <a href = "Mailto:u ' g@gmail.com '" target= "_blank" >u ' g@gmail.com ' </a>), (4L, U ' Facebook ', U ' 222333 ', <a href= " Mailto:u ' F@face.book ' "target=" _blank ">u ' F@face.book ' </a>)" &NBSP;


above this operation, is to start with the current position (where the pointer points to tuple's ordinal number 1, that is, the second record), with the current position and 3 records listed down.

Reads the data as if it's a little bit?? Egg-Jian o catch the cylinder talk ィ? So the cowardly view of the Miao and anxiety D-mei? Br/>
However, Python can always be considerate of us, its pointers provide a parameter that enables the reading of data into a dictionary form, which provides another way to read.

>>> cur = conn.cursor (cursorclass=mysqldb.cursors.dictcursor)      
>>> cur.execute ("Select * from users")       7l      >>> cur.fetchall ()       ({' username ':  u ' Qiwsir ',  ') Password ':  u ' 123123 ',  ' id ': 1l,  ' emails ':  <a href= ' mailto:u ' qiwsir@gmail.com '  target= "_blank" >u "qiwsir@gmail.com ' </a>}, {' username ':  u ' mypython ',  ' password ':  u ' 123456 ',  ' id ': 2l,  ' email ':  <a href= ' mailto:u ' ' python@gmail.com ' '   target= "_blank" >u ' python@gmail.com ' </a>}, {' username ':  u ' google ',  ' password ' U ' 111222 ',  ' id ': 3l,  ' email ':  <a href= ' mailto:u ' g@gmail.com '  target= ' _blank ' >u ' g@gmail.com ' </a>}, {' username ':  u ' Facebook ',  ' password ':  u ' 222333 ' ",  ' ID ':  4L,  ' email ':  <a href= "Mailto:u ' F@face.book" " target=" _blank ">u ' f@face.book ' </a>}, {'" Username ':  u ' github ',  ' password ':  u ' 333444 ' ',  ' id ': 5l,  ' email ': <a  href= "Mailto:u ' git@hub.com" " target=" _blank ">u ' git@hub.com ' </a>}, {' username '":  " U ' docker ',  ' password ':  u ' 444555 ',  ' id ': 6l,  ' email ':  <a href= ' mailto: U ' doc@ker.com ' " target=" _blank ">u ' doc@ker.com ' </a>}, {' username ':  u ' \u8001\u9f50 ',   ' password ':  u ' 9988 ',  ' id ': 7l,  ' email ':  <a href= ' mailto:u ' qiwsir@ gmail.com ' " target=" _blank ">u ' qiwsir@gmail.com ' </a>})

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

>>> cur.scroll (0, "absolute") >>> for line in
cur.fetchall ():
. Print Lin  e["username"]
...
Qiwsir
Mypython
google
Facebook
GitHub
Docker
old

Reads "Key-value" based on the characteristics of the Dictionary object.

Update data

After the previous operation, this is relatively simple, but it is necessary to remind that, if the update is complete, and insert the data, you need a commit () to submit 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 ', <a href= "Mailto:u ' python@gmail.com '" "target=" _blank "" >u ' python@gmail.com ' </a>)

As seen from the operation, the user name of the second article in the database has been modified to Mypython, with the UPDATE statement.

However, to really implement updates in the database, you also run:

>>> Conn.commit ()

This is the end of the big deal.




Python operation MySQL (add to check)

#!/usr/bin/env python #coding: utf-8   import mysqldbtry:     # Ways to connect to MySQL: Connect (' IP ', ' user ', ' password ', ' dbname ')      #conn =mysqldb.connect (host= ') localhost ', user= ' root ', passwd= ' 123456 ', db= ' test ') conn =mysqldb.connect (' 127.0.0.1 ', ' root ', ' 123456 ', charset =  ' gb2312 ') conn.select_db (' Python ') cur=conn.cursor () sql1 =  ' drop database  python '   #删除数据库   sql2 =  ' create database if not exists  Python '   #若不存在, the database is created sql3 =  ' Create database python ' sql4 =  ' create  Table module (M_id int not null,m_name varchar), m_size int) ' #创建表 sql5 =   ' Create table if not exists demo (d_id int not null,d_name  varchar, m_size int default 0) ' values=[]    for i in  Range (1):      VALUES.APpend ((i, ' MySQL ', i+1))   sql6 =  ' insert into module values (%s,%s,%s) ' # Cur.executemany (sql6,values)     #批量插入 values = [1, ' MySQLdb ', 5] sql6 =  ' Insert  into module values ('%d ', '%s ', '%d ')% (2, ' MySQLdb ', 7)   #插入 #sql6  =  "insert  Into module (m_id,m_name,m_size)  values ('%d ', '%s ', '%d ') "% (2, ' MySQLdb ', 7) #sql6  = " Insert  into module (m_id,m_name,m_size)  values ('%d ', '%s ', '%d ')% (values[0],values[1],values[2]) SQL7  =  "Update module set m_name= ' MySql '  where m_id=0 and m_size=0"   #修改 sql8 =  "delete from module where m_id=1 and m_size=0"  # Delete sql9 =  "select * from module where m_id=1" Cur.execute (SQL9) count =
 cur.execute (SQL9)    #查询结果数量 print u ' number of query results: ', Count Result = cur.fetchone ()   Print u ' Single query Result: ', ResULt Result = cur.fetchmany (2)     print u ' Multiple query results: ', result result =  Cur.fetchall ()     print u ' all different query results: ', result    for data
 in result:        print data Conn.commit () cur.close () Conn.close () except mysqldb.error,e:    print  "mysql error %d: %s"  %  (E.args[0], e.args[1])