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

本文介绍如何在Windows平台上下载并安装MySQL Connector Python,并通过示例代码演示如何连接数据库、创建表、插入和查询数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Install the MySQL Connector

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()

References:

### 如何使用Python连接并查询MySQL数据库 为了实现通过Python程序访问MySQL数据库的功能,可以采用多种库来完成这一操作。以下是基于`mysql-connector-python`和`pymysql`两种常见方式的具体实现。 #### 使用 `mysql-connector-python` 此方法依赖于官方支持的`mysql-connector-python`库[^1]。首先需要确保已安装该库: ```bash pip install mysql-connector-python ``` 随后可以通过以下代码片段建立与MySQL数据库的连接,并执行SQL查询语句: ```python import mysql.connector def connect_to_mysql(host, user, password, database): connection = mysql.connector.connect( host=host, user=user, password=password, database=database ) return connection def run_query(connection, query): cursor = connection.cursor() cursor.execute(query) results = cursor.fetchall() cursor.close() return results if __name__ == "__main__": db_connection = connect_to_mysql('localhost', 'root', 'password', 'test_db') sql_query = "SELECT * FROM employees" data = run_query(db_connection, sql_query) print(data) db_connection.close() ``` 以上代码展示了如何利用`mysql-connector-python`库连接至本地运行的MySQL实例,并从中读取名为`employees`表中的全部记录。 #### 使用 `PyMySQL` 另一种广泛使用的解决方案是借助第三方开发维护的`PyMySQL`模块[^2]。同样需先确认其已被正确安装: ```bash pip install PyMySQL ``` 接着按照下面的例子编写相应的逻辑处理函数: ```python import pymysql def establish_pymysql_link(server_address, username, secret_key, schema_name): link = pymysql.connect( host=server_address, user=username, password=secret_key, database=schema_name ) return link def perform_sql_operation(linkage, statement): pointer = linkage.cursor() pointer.execute(statement) outcomes = pointer.fetchall() pointer.close() return outcomes if __name__ == "__main__": pymyql_conn = establish_pymysql_link('127.0.0.1', 'admin', 'securepwd', 'sample_schema') sql_stmt = "SELECT id, name FROM customers WHERE active = TRUE;" fetched_data = perform_sql_operation(pymyql_conn, sql_stmt) for row in fetched_data: print(f"Customer ID: {row[0]}, Name: {row[1]}") pymyql_conn.close() ``` 这里提供了另一个例子展示怎样运用`PyMySQL`去获取激活状态下的客户列表信息。 无论选用哪种技术栈路径,在实际项目部署前都应充分测试以验证稳定性及安全性;同时注意保护敏感凭据不被泄露给未经授权的人士。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值