Python2.7 with MySQL Connector/Python Sample(Windows OS)

最新推荐文章于 2024-11-04 10:51:20 发布

JasonYao

最新推荐文章于 2024-11-04 10:51:20 发布

阅读量985

点赞数

分类专栏： Python 文章标签： python mysql

本文链接：https://blog.csdn.net/yaominhua/article/details/50718080

版权

Python 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Install the MySQL Connector

Download URL: https://dev.mysql.com/downloads/connector/python/
Platform: Microsoft Windows
Download Version: Windows (x86, 32-bit), MSI Installer Python 2.7
Download Package Name: mysql-connector-python-2.1.3-py2.7-win32.msi

Install package

Double click the package and click the Run button to complete installation.

Test if installed successfully

Open windows command line

py
>>> import mysql.connector

If there is no error then it is successful.

Code with example

import mysql.connector
from mysql.connector import errorcode
database = 'testpython'

def get_db_connectoin(database):
    username = 'root'
    password = '******'
    hostname = 'localhost'
    port = 3306
    database = database
    connection = None 
    try:
        connection = mysql.connector.connect(user=username, password=password, host=hostname, port=port, database=database)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print 'Username,Password Error'
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print 'Database does not exist'
            connection = mysql.connector.connect(user=username, password=password, host=hostname, port=port)
            cursor = connection.cursor()
            cursor.execute("CREATE DATABASE "+ database + " DEFAULT CHARACTER SET 'utf8'")
        else:
            print err
    else:
        connection.database = database
    return connection

def create_table(connection, sql):
    cursor = connection.cursor()
    try:
        cursor.execute(sql)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print "Table already exist"
        else:
            print err
    else:
        cursor.close()
        connection.close()
    return

def inset_data(connection, sql, args):
    cursor = connection.cursor()
    try:
        cursor.execute(sql, args)
        connection.commit()
    except mysql.connector.Error as err:
        print err
    else:
        cursor.close()
        connection.close()
    return

def query_data(connection, sql, args):
    cursor = connection.cursor()
    cursor.execute(sql, args)
    return cursor

connection = get_db_connectoin(database)
createSql = ("CREATE TABLE `TEST_TABLE` ("
             "`ID` INT(11) PRIMARY KEY AUTO_INCREMENT,"
             "`NAME` VARCHAR(64) NOT NULL,"
             "`AGE` INT(3))"
            )
create_table(connection=connection, sql=createSql)

connection = get_db_connectoin(database)
insetSql = "INSERT INTO TEST_TABLE(NAME,AGE) VALUES (%(NAME)s,%(AGE)s)" 
args = {'NAME':'jason','AGE':28}
inset_data(connection=connection, sql=insetSql, args=args)

connection = get_db_connectoin(database)
querySql = "SELECT * FROM TEST_TABLE WHERE NAME=%s AND AGE > %s"
args = ("jason", 26)
cursor = query_data(connection=connection, sql=querySql, args=args)
for (id, name, age) in cursor:
    print id
    print name
    print age
    print '\n'
cursor.close()
connection.close()