| |
Establish a database connection import MySQLdb
conn = MySQLdb.connect (host = "localhost", user = "root", passwd = "sa", db = "mytable")
More commonly used parameters include
host: the database server host name to connect, the default is the local host (localhost).
user: the database login name defaults to the current user.
passwd:. login secret database is empty by default.
db: name of the database you want to use with no default.
port:. MySQL service uses TCP port 3306 by default.
conn connection There are two important ways to add and modify commit [submit], rollback [withdraw] to add or modify
3, perform database operations
n = cursor.execute (sql, param)
We want to use the connection object to get a cursor object Next, we'll use the cursor to provide work.
These include two categories: 1 Run 2 receives the return value.
cursor method is used to execute commands:
callproc (self, procname, args): used to execute a stored procedure, the received parameters for the stored procedure name and parameter list, the return value is the number of affected rows
execute (self, query, args): parameter performs a single sql statement sql statement itself is received and a list of parameters used, the return value is the number of affected rows
executemany (self, query, args): execute sql statement singled out, but repeated the argument list of parameters executed, the return value is the number of affected rows
nextset (self): move to the next result set
cursor for receiving return values:
fetchall (self): return receive all the result rows.
fetchmany (self, size = None): receiver size bar return result rows if the value is greater than the size of the number of results returned rows of data will be returned cursor.arraysize.
fetchone (self): return a result rows.
scroll (self, value, mode = 'relative'):. Move the pointer to a row if mode = 'relative', it means that the current line move value item, if mode = 'absolute', said the first results from the set line of mobile value bars.
The following code is a complete example.
# Using sql statement, where parameters are to be received by% s placeholder is to be noted that, whether you want to insert the data of what type, the placeholder should always use% s
sql = "insert into cdinfo values (% s,% s,% s,% s,% s)"
#param be tuple or list
param = (title, singer, imgurl, url, alpha)
# Execution, if successful, n value of 1
n = cursor.execute (sql, param)
# Again to perform a query operation
cursor.execute ( "select * from cdinfo")
# Fetchall we use this method. Thus, cds will be stored in all the results returned by the query. Each result is a tuple type of data that make up a tuple tuple
cds = cursor.fetchall ()
# Because it is tuple, so we can use this result set
print cds [0] [3]
# Or directly displayed, look at the real appearance of the result set
print cds
# If you need to bulk insert data, to do so
sql = "insert into cdinfo values (0,% s,% s,% s,% s,% s)"
# Set each value is a tuple, the entire parameter set consisting of a tuple, or list
param = ((title, singer, imgurl, url, alpha), (title2, singer2, imgurl2, url2, alpha2))
# Use executemany way to bulk insert data. This is a really cool way!
n = cursor.executemany (sql, param)
Note that (or that I am surprised) completes execution after the insertion or deletion or modification, you need to call about conn.commit () method to commit. Thus, the data will actually stored in the database. I unclear whether my mysql setup problems, in short, today I used at the beginning, if you do not commit, that data is not retained in the database, but the data in the database actually spent because automatic numbering has been accumulated and the number of rows affected is not returned to zero.
4, close the database connection
Close pointer need separate objects and connection objects. They have the same name method
cursor.close ()
conn.close ()
5 Coding (prevent distortion)
Note that point:
1 Python settings file encoding utf-8 (in front of the file plus # encoding = utf-8)
2 MySQL database charset = utf-8
3 Python MySQL connection is to add parameters charset = utf8
4 Set Python's default encoding is utf-8 (sys.setdefaultencoding (utf-8)
# Encoding = utf-8
import sys
import MySQLdb
reload (sys)
sys.setdefaultencoding ( 'utf-8')
db = MySQLdb.connect (user = 'root', charset = 'utf8')
Note: MySQL configuration file settings must also be configured to utf8
6. The module Demo
[Root @ localhost python] # more mysql.py
#! / Usr / bin / python
import module import MySQLdb ###
conn = MySQLdb.connect (user = "root", passwd = "ESBecs00! @ #", db = "test") ### connected to mysql
cursor = conn.cursor () ### a link cursor
sql = "select * from test" ### sql statement
n = cursor.execute (sql) ### execute sql
print n ### is executed successfully returns n (n represents the number of impacts, which is 3)
row = cursor.fetchall () ### fetchall () Returns all live results, fetchone () returns a live result
print row ### to print out the return results
print "################################################ #########Dividing line####################################### ########### "
cursor.execute ( '' '### can also be placed directly inside the sql statement execution
create table if not exists food (
id int (10) primary key,
name varchar (20),
age int (10)
)
'' ')
sql = "desc food"
cursor.execute (sql)
row = cursor.fetchall ()
print row
cursor.close () ### Close the cursor
conn.close () ### closes the connection
Results of the
[Root @ localhost python] # python mysql.py
3
(( 'Zhangsan',), ( 'lisi',), ( 'wangwu',))
################################################## #######
mysql.py:21: Warning: Table 'food' already exists
'' ')
(( 'Id', 'int (10)', 'NO', 'PRI', None, ''), ( 'name', 'varchar (20)', 'YES', '', None, '' ), ( 'age', 'int (10)', 'YES', '', None, '')
Execute the following SQL statement to capture the return value:
// Get the cursor connection
cursor = conn.cursor ()
//Inquire
sql = "select * from [table]."
cursor.execute (sql)
return value
cur.execute ( 'select * from tables')
Returns the number of rows to get the value of SQL statements, such as: 2L, represents a row.
Then, line information can be obtained from fetchone or fetchall method of that object.
Get line information
fetchone object pointer () method, is to give each row tuple return value:
Quote
>>> Row = cur.fetchone ()
>>> Print row
( 'User1', '52c69e3a57331081823331c4e69d3f2e', 1000L, 1000L, '/ home / FTP / user1', '')
fetchall object pointer () method, you can remove all rows in the result set pointer, the result set returns a tuple (tuples):
Quote
>>> Cur.scroll (0, 'absolute')
>>> Row = cur.fetchall ()
>>> Print row
(( 'User1', '52c69e3a57331081823331c4e69d3f2e', 1000L, 1000L, '/ home / FTP / user1', ''), ( 'user2', '7e58d63b60197ceb55a1c487989a3720', 1000L, 1000L, '/ home / FTP / user2', None ))
Move the pointer
When fetchone () method, the pointer is moved occur. So, if not reset the pointer, use fetchall information will only contain the line pointer back.
Manually move the pointers:
cur.scroll (int, parm)
Meaning:
Quote
int: The number of lines is an integer; in relative mode, positive move downwards, negative values indicate movement upward.
parm: moving mode, the default is relative, relative mode; acceptable absoulte, absolute mode.
Modify data
Modify data, including insert, update, delete. They are using a pointer object execute () method is executed:
cur.execute ( "insert into table (row1, row2) values ( '111', '222')")
cur.execute ( "update table set row1 = 'test' where row2 = 'row2'")
cur.execute ( "delete from table where row1 = 'row1'")
Because single quote " '" is used to identify the SQL statement, therefore, python string need to use double quotes.
Alternatively, you can use the python "format string" writing, simplified command, for example:
cur.execute ( "update table set row1 = '% s' where row2 = '% s'"% ( 'value1', 'value2'))
Please note that, '% s' single quotation marks are spaced characters SQL statements, 'value1' single quotation marks are python string break character, its meaning is different. Need break character, and use double quotes or single quotes as a spacer, to be determined according to its meaning. For example, there are:
cur.execute ( "update FTPUSERS set passwd =% s where userid = '% s'"% ( "md5 ( '123')", 'user2'))
Here, paswd =% s is due to SQL's md5 () function does not require single quotes interval; "md5 ( '123')" is a string containing a python in single quotes, double quotes around it.
Submit modification
Under normal circumstances, MySQLdb module automatically commit changes. But after we update data manually run again:
conn.commit ()
Close the database connection
Close pointer need separate objects and connection objects. They have the same name method
cursor.close ()
conn.close () |
|
|
|