How to connect PYTHON3 to the MYSQL database

There are a lot of MySQL-connected libraries on Python 2.x, notably MySQLdb (which is used by Django projects; I also used it when developing test systems), see: http://sourceforge.net/projects/mysql-python/

However, at present, MYSQLDB does not support python3.x, the Internet to find some methods, and then I accidentally found that the MySQL official has provided a MySQL connector, and already has a support for the python3.x version. MySQL Connector/python, a self-contained Python driver for communicating with MySQL servers. This is still more comfortable to use.

About MySQL Connector/python of various introduction, installation, API and other documents, or reference official Internet cafes:
Http://dev.mysql.com/doc/connector-python/en/index.html

(Note: The installer will copy the Python2 source files about the MySQL connnector to the Python3 library (runtime will report a syntax error), and I will manually replicate the files in the python3/directory in the past to solve them. ）

In addition, python3.x connection MySQL Other options are: Oursql, Pymysql, myconnpy, etc., refer to the following links:

http://packages.python.org/oursql/

https://github.com/petehunt/PyMySQL/

Https://launchpad.net/myconnpy

Establish a connection with 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 with the database, receive several parameters, and return the connection object.

The more commonly used parameters include

Host: Database host name. The default is to use the local host.

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

PASSWD: The Secret of database login. Null default.

DB: The name of the database to use. There is no default value.

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

CharSet: Database encoding.

Here's a Python script that tries MySQL Connector/python (including creating tables, inserting data, reading from files, inserting data, querying data, and so on):

#!/usr/bin/python3
# A sample to use Mysql-connector for Python3
# The details from http://dev.mysql.com/doc/connector-python/en/index.html

Import Mysql.connector
Import sys, OS

user = ' root '
PWD = ' 123456 '
Host = ' 127.0.0.1 '
db = ' Test '

data_file = ' Mysql-test.dat '

Create_table_sql = "CREATE table IF not EXISTS mytable (\
ID Int (TEN) auto_increment PRIMARY KEY, \
Name varchar, age int (4)) \
CHARACTER SET UTF8 "

Insert_sql = "INSERT into MyTable (name, age) VALUES (' Jay ', 22), (' Jay ', 26)"
Select_sql = "SELECT ID, Name, age from MyTable"

CNX = Mysql.connector.connect (User=user, Password=pwd, Host=host, database=db)
cursor = Cnx.cursor ()

Try
Cursor.execute (Create_table_sql)
Except Mysql.connector.Error as err:
Print ("CREATE table ' MyTable ' failed.")
Print ("Error: {}". Format (ERR.MSG))
Sys.exit ()

Try
Cursor.execute (Insert_sql)
Except Mysql.connector.Error as err:
Print ("Insert Table ' mytable ' failed.")
Print ("Error: {}". Format (ERR.MSG))
Sys.exit ()

If Os.path.exists (data_file):
MyFile = open (data_file)
lines = Myfile.readlines ()
Myfile.close ()

For line in lines:
MySet = Line.split ()
sql = "INSERT into MyTable (name, age) VALUES (' {} ', {} ')". Format (Myset[0], myset[1])
Try
Cursor.execute (SQL)
Except Mysql.connector.Error as err:
Print ("Insert Table ' mytable ' from file ' Mysql-test.dat '--failed.")
Print ("Error: {}". Format (ERR.MSG))
Sys.exit ()

Try
Cursor.execute (Select_sql)
For [ID, name, age] in cursor:
Print ("id:{} name:{} age:{}". Format (ID, Name, age)
Except Mysql.connector.Error as err:
Print ("Query table ' mytable ' failed.")
Print ("Error: {}". Format (ERR.MSG))
Sys.exit ()

Cnx.commit ()
Cursor.close ()
Cnx.close ()

In addition, finally put a python2.x code example that uses MYSQLDB:

#!/usr/bin/python2.7
# coding=utf-8
 
Import mysqldb
Import sys
 
host = ' localhost '
user = ' root '
pwd  = ' 123456 '    # to IS modified.
db   = ' test '
 
  ;
If __name__ = = ' __main__ ':
    conn = MySQLdb.connect (host, user, PWD, db, charset= ' UTF8 ');
&N bsp;   try:
        conn.ping ()
    except:
        print ' failed to connect MySQL. '
    sql = ' SELECT * FROM mytable where id = 2 '
    cur = conn.cursor ()
 &N bsp;  cur.execute (SQL)
    row = Cur.fetchone ()
#    print type (row)
& nbsp;   for I in row:
        print i
    cur.close ()
    conn.close ()
    sys.exit ()

Using Python3 to manipulate MySQL

The following is an example of a python3 operation MySQL5.5, which includes connecting to MySQL, creating a database, creating a table, inserting/querying data features:

Copy Code
#-*-Coding:utf-8-*-
# author:txw1958
# website:http://www.cnblogs.com/txw1958/

Import MySQLdb

#连接
CxN = MySQLdb.connect (host = ' 127.0.0.1 ', user = ' root ', passwd = ' Welcome123 ')
#游标
cur = cxn.cursor ()

Try
Cur.execute ("DROP DATABASE txw1958")
Except Exception as E:
Print (e)
Finally
Pass

#创建数据库
Cur.execute ("CREATE DATABASE txw1958")
Cur.execute ("Use txw1958")

#创建表
Cur.execute ("CREATE TABLE users" (ID INT, name VARCHAR (8))
#插入
Cur.execute ("INSERT into Users VALUES (1, ' www '), (2, ' cnblogs '), (3, ' com '), (4, ' txw1958 ')")
#查询
Cur.execute ("SELECT * from users")
For row in Cur.fetchall ():
Print ('%s\t%s '%row)

#关闭
Cur.close ()
Cxn.commit ()
Cxn.close ()

The results of the operation are as follows:

C:\>python py3-mysql.py
1 www
2 Cnblogs
3 com
4 txw1958

C:\>