Examples of Python using MySQL databases

One, install MySQL

If it is a Windows user, MySQL installation is very simple, download the installation files directly, double-click the installation file step-by-step operation can be.

Linux installation may be more simple, in addition to downloading installation package for installation, the General Linux warehouse will have MySQL, we only need to use a command to download the installation:

Ubuntu\deepin

>>sudo Apt-get Install Mysql-server

>>sudo Apt-get Install Mysql-client

Centos/redhat

>>yum install MySQL

Second, install Mysql-python

To enable Python to operate MySQL requires a Mysql-python driver, which is a necessary module for Python to manipulate MySQL.

Download Address: https://pypi.python.org/pypi/MySQL-python/

After downloading the Mysql-python-1.2.5.zip file, unzip it directly. Enter mysql-python-1.2.5 directory:

>>python setup.py Install

Third, connect MySQL database.

I am using the MySQL database for MYSQLDB operations. Let's start with a simple example:

Import MySQLdb

Try
Conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' test ', port=3306)
Cur=conn.cursor ()
Cur.execute (' select * from user ')
Cur.close ()
Conn.close ()
Except Mysqldb.error,e:
Print "Mysql Error%d:%s"% (E.args[0], e.args[1])
Please note that you modify your database, host name, username, password.

Here's a rough example of inserting data, bulk inserting data, and updating data:

Import MySQLdb

Try
Conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', port=3306)
Cur=conn.cursor ()

Cur.execute (' Create database if not exists python ')
conn.select_db (' python ')
Cur.execute (' CREATE TABLE test (ID int,info varchar (20)) ')

value=[1, ' Hi Rollen ']
Cur.execute (' INSERT into test values (%s,%s) ', value)

Values=[]
For I in range (20):
Values.append ((i, ' Hi Rollen ' +str (i)))

Cur.executemany (' INSERT into test values (%s,%s) ', values)

Cur.execute (' Update test set info= ' I am Rollen "where id=3 ')

Conn.commit ()
Cur.close ()
Conn.close ()

Except Mysqldb.error,e:
Print "Mysql Error%d:%s"% (E.args[0], e.args[1])
Please note that you must have Conn.commit () to commit the transaction, or you cannot actually insert the data.

After running my MySQL database results are not on the map.

Import MySQLdb

Try
Conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', port=3306)
Cur=conn.cursor ()

conn.select_db (' python ')

Count=cur.execute (' SELECT * from Test ')
print ' There has%s rows record '% count

Result=cur.fetchone ()
Print result
print ' ID:%s info%s '% result

Results=cur.fetchmany (5)
For R in results:
Print R

print ' = = ' *10
Cur.scroll (0,mode= ' absolute ')

Results=cur.fetchall ()
For R in results:
Print R[1]


Conn.commit ()
Cur.close ()
Conn.close ()

Except Mysqldb.error,e:
Print "Mysql Error%d:%s"% (E.args[0], e.args[1])
The results of the operation will not be affixed, too long.

After the query Chinese will be displayed correctly, but in the database is garbled. After I looked up from the internet, I found a property with a can be handled:

In the Python code

conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' python ') add an attribute:
To
conn = MySQLdb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' python ', charset= ' UTF8 ')
CharSet is to be the same as your database code, if the database is gb2312, then write charset= ' gb2312 '.

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.

Four

Code (prevent garbled)

Points to note:

1 python file Set encoding Utf-8 (preceded by #encoding =utf-8)
2 MySQL Database charset=utf-8
3 python connection mysql is plus parameter Charset=utf8
4 Setting the default encoding for Python is Utf-8 (sys.setdefaultencoding (Utf-8)

1. #encoding =utf-8 2. Import SYS 3.  Import MySQLdb 4. 5. Reload (SYS) 6.  Sys.setdefaultencoding (' Utf-8 ') 7. 8. Db=mysqldb.connect (user= ' root ', charset= ' UTF8 ')
Note: MySQL configuration file settings must also be configured to UTF8

6. Module Function Demo
#!/usr/bin/python
Import MySQLdb

con= mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' root ', db= ' abc ')

Cursor =con.cursor ()

sql = "SELECT * from Myt"

Cursor.execute (SQL)

Row=cursor.fetchone ()

Print row

Cursor.close ()

Con.close ()