python-python链接数据库mysql

环境:mac os

python DB API

Python访问数据库的统一接口规范
1.创建数据库连接对象conection
2.获取数据库交互对象cursor
3.查询,插入数据等操作
4关闭cursor,connection

下载mysql connector

pip3 install mysql-connector-python

创建连接对象connection

建立python与mysql之间的连接
创建方法:MySQLdb.Connec(参数)

connection对象支持的方法:

• cursor()使用该连接创建返回游标
• commit()提交
• rollback()回滚
• close()关闭连接

代码

import mysql.connector
from mysql.connector import errorcode
config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': '*********',
    'port': 3306,
    'database': 'all_test',
    'charset': 'utf8'
}
try:
    print('开始连接mysql')
    conn = mysql.connector.connect(**config)
    print(conn)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("用户名或密码错误")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("数据库不存在")
    else:
        print(err)
cursor = conn.cursor()
try:
    sql_query = 'select * from stu ;'
    cursor.execute(sql_query)
    for name, age in cursor:
        print('[', name, age, ']')
except mysql.connector.Error as e:
    print('query error!{}'.format(e))
finally:
    print("关闭数据库连接")
    cursor.close()
    conn.close()

output:

开始连接mysql
<mysql.connector.connection.MySQLConnection object at 0x108df60b8>
[ 你好 78 ]
[ 学生啊 18 ]
关闭数据库连接

cursor游标对象(用于查询和获取结果)

查询语句注意:游标位置在移动,rowcount变

	sql_query = 'select * from stu ;'
    cursor = conn.cursor()
    cursor.execute(sql_query)
    print(cursor)
    print(cursor.rowcount)
    # for name, age in cursor:
    #     print('[', name, age, ']')
    print(cursor.fetchone())
    print(cursor.fetchmany(2))
    print(cursor.rowcount)
    print(cursor.fetchall())

插入语句

import mysql.connector
from mysql.connector import errorcode
config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': '1045152332zjy',
    'port': 3306,
    'database': 'all_test',
    'charset': 'utf8'
}
def select2(sql_cmd, param):
    """
    :param sql_cmd sql 命令
    :param param 参数
    """
    try:
        conn = mysql.connector.connect(**config)
    except mysql.connector.Error as e:
        print('connect fails!{}'.format(e))

    cursor = conn.cursor()
    try:
        cursor.execute(sql_cmd, param)
        conn.commit()
    except mysql.connector.Error as e:
        print('connect fails!{}'.format(e))
    finally:
        cursor.close()
        conn.close()

if __name__=='__main__':
    sql_cmd = "insert into stu (name, age) value (%s, %s)"
    param = ('name999', 999)
    select2(sql_cmd=sql_cmd, param=param)