Example of querying and updating data in python Mysql

Python operations MySQL, very convenient, using the MYSQLDB library, the basic operation is as follows:

Inquire:

1 Try:
2 conn = MySQLdb.connect (Host=self.ip, User=self.username,passwd=self.password, Db=self.dbname, Port=self.port)
3 cur = conn.cursor ()
4 Cur.execute (SQL)
5 rows = Cur.fetchall ()
6 data = rows
7 except Mysqldb.error, E:
8 Print str (e)
9 print "connet MySQL db error ..."
Ten Sys.exit ()

Insert data:

Try
conn = MySQLdb.connect (Host=self.ip, User=self.username, Passwd=self.password, Db=self.dbname, Port=self.port)
cur = conn.cursor ()
Cur.execute (SQL, value)
Conn.commit ()
Conn.close ()
Cur.close ()
Except Mysqldb.error, E:
Print str (e)
print "Execute MySQL db error ..."
Sys.exit ()

A coding problem was encountered during use, using Utf-8 to solve the coding problems:

Conn.set_character_set (' UTF8 ')
Cur.execute (' SET NAMES utf8; ')
Cur.execute (' Set CHARACTER set UTF8; ')
Cur.execute (' SET Character_set_connection=utf8; ')

Also encountered the problem of backslash, MySQL default to the backslash escaped, my solution is to replace the backslash double backslash:

DataPath = datapath.replace (' \ \ ', ' \\\\ ')
The MySQL statement needs to format the string, and the query's SQL string needs to represent the variable placeholder in%, but Python seems to have to use%s

Executemany also supports multiple data inserts at the same time, but I'm not using this because it's also convenient to recycle outside.

The following example links MySQL's TestDB database:

#!/usr/bin/python
#-*-Coding:utf-8-*-

Import MySQLdb

# Open Database connection
db = MySQLdb.connect ("localhost", "testuser", "test123", "TestDB")

# Use the cursor () method to get the action cursor
cursor = Db.cursor ()

# Execute SQL statement using the Execute method
Cursor.execute ("SELECT VERSION ()")

# Use the Fetchone () method to get a database.
data = Cursor.fetchone ()

Print "Database version:%s"% data

# Close Database connection
Db.close ()
The result of executing the above script output is as follows:
Database version:5.0.45
Create a database table
If a database connection exists we can use the Execute () method to create a table for the database, as shown in the following CREATE TABLE employee:
#!/usr/bin/python
#-*-Coding:utf-8-*-

Import MySQLdb

# Open Database connection
db = MySQLdb.connect ("localhost", "testuser", "test123", "TestDB")

# Use the cursor () method to get the action cursor
cursor = Db.cursor ()

# If a datasheet already exists use the Execute () method to delete the table.
Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE")

# Create a datasheet SQL statement
sql = "" "CREATE TABLE EMPLOYEE (
First_Name CHAR (not NULL),
Last_Name CHAR (20),
Age INT,
SEX CHAR (1),
Income FLOAT) "" "

Cursor.execute (SQL)

# Close Database connection
Db.close ()

Here are some common functions to post:

Then, this connection object also provides support for transaction operations, standard methods
Commit () Commit
Rollback () rollback

Cursor the method used to execute the command:
Callproc (self, procname, args): Used to execute stored procedures, the received parameters are stored procedure names and parameter lists, and the return value is the number of rows affected
Execute (Self, query, args): Executes a single SQL statement, receives the parameter is the SQL statement itself and uses the parameter list, returns the value is the number of rows affected
Executemany (self, Query, args): Executes a singled out SQL statement, but repeats the arguments in the parameter list, returning the value to the number of rows affected
Nextset (self): moving to the next result set

Cursor the method used to receive the return value:
Fetchall (self): receives all returned result rows.
Fetchmany (self, 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 (self): Returns a result row.
Scroll (self, 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.