Python3 Operation MySQL Tutorial

First, download \ Install \ Configuration

1. Python3

Python3 Download URL: http://www.python.org/getit/

Currently the latest version is python3.2, which is

Http://www.python.org/ftp/python/3.2.3/python-3.2.3.msi

Needless to say, the installation process is installed in the C:\Python32 directory by default.

Once installed, add the installation directory C:\Python32 to the environment variable. Then open a Command Prompt window, enter Python, if you can return to the Python version stating that the installation was successful and that the environment variable was set successfully.

c:\> on Win32Type for

2. MySQL

mysql:http://www.mysql.com/downloads/mysql/

MySQL has many kinds of versions, here we choose MySQL Community Server, the latest version 5.5.25a

: Http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.25a-win32.msi

The installation process is a bit complicated and you can refer to the MySQL installation diagram:

Http://wenku.baidu.com/view/fe9b292e4b73f242336c5fe9.html

Note that the MySQL encoding must be set to UTF8

After the installation is complete, you need to configure the MySQL, here I configure its user name is root, password Welcome123.

Log in to MySQL with the command, install successfully

C +>Mysql-U root-penter Password:**********Welcome toThe MySQL Monitor. CommandsEnd  with;or\g.your MySQL Connection ID is TenServer Version:5.5. 25a MySQL Community Server (GPL) Copyright (c) -, ., Oracleand/orits affiliates. Allrights reserved. Oracle isA registered trademark ofOracle Corporationand/orItsaffiliates. Names May trademarks oftheir respective owners. Type'Help ;' or '\h'  forHelp. Type'\c'  toClear the Currentinput Statement.mysql>

Here we create a database named txw1958.

Mysql> Create Databasetxw1958; Query OK,1Row affected (0.03sec) MySQL>show databases;+--------------------+| Database           |+--------------------+|Information_schema||Mysql||Performance_schema||Test||txw1958|+--------------------+6Rowsinch Set(0.00Sec

3. Mysql-python Module

Mysql-python is MySQL's database interface for Python. Currently supports python2.7 and python3.2.

: http://download.csdn.net/detail/txw1958/5818181

During the installation process, you can automatically find the installation directory of python3.2 and install it under this directory.

After the installation is complete, import mysqldb in Python, if there is no error, the installation is successful.

Import MySQLdb

Ii. using Python3 to operate MySQL

Here is an example of a python3 operation MySQL5.5 that includes connecting MySQL, creating a database, creating tables, inserting/querying data functions:

#-*-coding:utf-8-*-#author:txw1958#website:http://www.cnblogs.com/txw1958/ImportMySQLdb#ConnectionCxN = MySQLdb.connect (host ='127.0.0.1', user ='Root', passwd ='Welcome123')#CursorsCur =cxn.cursor ()Try: Cur.execute ("DROP DATABASE txw1958")exceptException as E:Print(e)finally:    Pass#Create a databaseCur.execute ("CREATE DATABASE txw1958") Cur.execute ("Use txw1958")#Create a tableCur.execute ("CREATE TABLE users (id INT, name VARCHAR (8))")#InsertCur.execute ("INSERT into Users VALUES (1, ' www '), (2, ' cnblogs '), (3, ' com '), (4, ' txw1958 ' )")#EnquiryCur.execute ("SELECT * from Users") forRowinchCur.fetchall ():Print('%s\t%s'%row)#Closecur.close () cxn.commit () Cxn.close ( )

For MySQLdb's introduction and API, please refer to http://mysql-python.sourceforge.net/MySQLdb.html

The results of the operation are as follows:

C:\>python Py3-mysql. py1       www2       cnblogs3       com4       txw1958c:\>

Related information of MySQLdb

1. Introduction of MYSQLDB Library

Import MySQLdb

2. Establish a connection to the database

Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "sa", db= "MyTable", charset= "UTF8")

The Connect method provided is used to establish a connection to the database, receive several parameters, and return the connection object.

The more commonly used parameters include

Host: the database hostname. By default, the local host is used.

User: Database login name. The default is the current user.

PASSWD: The Secret of database landing. Default is empty.

DB: The name of the database to use. No default value.

The TCP port used by the Port:mysql service. The default is 3306.

CharSet: Database encoding.

More information on the parameters can be found here.

Http://mysql-python.sourceforge.net/MySQLdb.html

The connection object is then provided with support for transactional operations, and the standard method

Commit () Commit

Rollback () rollback

3. Execute SQL statements and receive return values

Cursor=conn.cursor ()

N=cursor.execute (Sql,param)

First, we use the connection object to get a cursor object, and then we use the method provided by the cursor to do the work. These methods include two main classes: 1. Execute command, 2. Receive return value

Cursor the method used to execute the command:

Callproc (self, procname, args): Used to execute stored procedure, received parameter is stored procedure name and parameter list, return value is the number of rows affected

Execute (Self, query, args): Executes a single SQL statement, receives the parameters for the SQL statement itself and the parameter list used, and returns the number of rows affected

Executemany (self, Query, args): Executes a single SQL statement, but repeats the parameters in the list of parameters, with the returned value being the number of rows affected

Nextset (self): move to the next result set

The cursor is used to receive the return value of the method:

Fetchall (self): receives all the returned result rows.

Fetchmany (self, Size=none): Receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, the cursor.arraysize data is returned.

Fetchone (self): Returns a result row.

Scroll (self, value, mode= ' relative '): Moves the pointer to a row. If mode= ' relative ', the value bar is moved from the current row, if mode= ' absolute ', Represents the move value bar from the first row of the result set.

The following code is a complete example.

#使用sql语句, the parameters to be received here are in the%s placeholder. Note that no matter what type of data you want to insert, the placeholder will always use%s

Sql= "INSERT into cdinfo values (%s,%s,%s,%s,%s)"

#param应该为tuple或者list

Param= (Title,singer,imgurl,url,alpha)

#执行, if successful, the value of n is 1

N=cursor.execute (Sql,param)

#再来执行一个查询的操作

Cursor.execute ("SELECT * from Cdinfo")

#我们使用了fetchall这个方法. In this way, the CDs will be saved as the full result of the query return. Each result is a tuple-type data that consists of a tuple

Cds=cursor.fetchall ()

#因为是tuple, so you can use result sets like this

Print Cds[0][3]

#或者直接显示出来 to see what the result set looks like.

Print CDs

#如果需要批量的插入数据, just do it.

Sql= "INSERT into cdinfo values (0,%s,%s,%s,%s,%s)"

#每个值的集合为一个tuple, the entire set of parameters consists of a tuple, or list

Param= ((Title,singer,imgurl,url,alpha), (TITLE2,SINGER2,IMGURL2,URL2,ALPHA2))

#使用executemany方法来批量的插入数据. This is a really cool way!

N=cursor.executemany (Sql,param)

4. Close the database connection

You need to close the pointer object and the connection object separately. They have the same name.

Cursor.close ()

Conn.close ()

Python3 Operation MySQL Tutorial