python Mysql数据库连接

最新推荐文章于 2023-01-13 16:35:35 发布

Janus_V

最新推荐文章于 2023-01-13 16:35:35 发布

阅读量328

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/qq_42683011/article/details/113814531

版权

python 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

MySQL数据库连接:

参考博客:

https://www.cnblogs.com/liubinsh/p/7568423.html

这里使用pymysql包, 需要使用pip预先安装, 安装过程就不阐述了

基本的流程与JDBC相似

栗子:

import pymysql

if __name__ == "__main__":
    # 确定连接参数
    DB_URL = "localhost"
    DB_NAME = "employee"
    DB_USERNAME = "root"
    DB_PASSWORD = "1234"
    # 连接数据库
    db = pymysql.connect(DB_URL, DB_USERNAME, DB_PASSWORD, DB_NAME)
    # 创建游标对象
    cursor01 = db.cursor()
    # 使用游标对象执行SQL语句
    cursor01.execute("SELECT * FROM employee_table_1")
    # 获取返回的数据
    data = cursor01.fetchall()
    # 逐条打印数据
    for tmp in data:
        print(tmp)
    # 清理
    cursor01.close()
    db.close()

建立物理连接:

# 确定连接参数
DB_URL = "localhost"
DB_NAME = "employee"
DB_USERNAME = "root"
DB_PASSWORD = "1234"
# 连接数据库
db = pymysql.connect(DB_URL, DB_USERNAME, DB_PASSWORD, DB_NAME)

通常简单的连接一个数据库只需要使用这4个参数, connect支持的参数很多, 基本上的使用不离这几种:

其余的直接现查现用了:

def __init__(self, host=None, user=None, password="",
             database=None, port=0, unix_socket=None,
             charset='', sql_mode=None,
             read_default_file=None, conv=None, use_unicode=None,
             client_flag=0, cursorclass=Cursor, init_command=None,
             connect_timeout=10, ssl=None, read_default_group=None,
             compress=None, named_pipe=None, no_delay=None,
             autocommit=False, db=None, passwd=None, local_infile=False,
             max_allowed_packet=16*1024*1024, defer_connect=False,
             auth_plugin_map={}, read_timeout=None, write_timeout=None,
             bind_address=None):
参数解释：
host: Host where the database server is located    #主机名或者主机地址
user: Username to log in as   #用户名
password: Password to use.    #密码
database: Database to use, None to not use a particular one.    #指定的数据库
port: MySQL port to use, default is usually OK. (default: 3306)    #端口，默认是3306
bind_address: When the client has multiple network interfaces, specify
    the interface from which to connect to the host. Argument can be
    a hostname or an IP address.    #当客户端有多个网络接口的时候，指点连接到数据库的接口，可以是一个主机名或者ip地址
unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
charset: Charset you want to use.    #指定字符编码
sql_mode: Default SQL_MODE to use. 
read_default_file:
    Specifies  my.cnf file to read these parameters from under the [client] section.
conv:
    Conversion dictionary to use instead of the default one.
    This is used to provide custom marshalling and unmarshaling of types.
    See converters.
use_unicode:
    Whether or not to default to unicode strings.
    This option defaults to true for Py3k.
client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass: Custom cursor class to use.
init_command: Initial SQL statement to run when connection is established.
connect_timeout: Timeout before throwing an exception when connecting.
    (default: 10, min: 1, max: 31536000)
ssl:
    A dict of arguments similar to mysql_ssl_set()'s parameters.
    For now the capath and cipher arguments are not supported.
read_default_group: Group to read from in the configuration file.
compress; Not supported
named_pipe: Not supported
autocommit: Autocommit mode. None means use server default. (default: False)
local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
    Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
defer_connect: Don't explicitly connect on contruction - wait for connect call.
    (default: False)
auth_plugin_map: A dict of plugin names to a class that processes that plugin.
    The class will take the Connection object as the argument to the constructor.
    The class needs an authenticate method taking an authentication packet as
    an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used
    (if no authenticate method) for returning a string from the user. (experimental)
db: Alias for database. (for compatibility to MySQLdb)
passwd: Alias for password. (for compatibility to MySQLdb)

参数

实行查询:

创建cursor对象:

cursor(光标)对象用于与数据库交互, 是提交查询请求的接口, 所以需要实例化一个对象:

# 创建游标对象
cursor01 = db.cursor()

cursor中的方法:

class Cursor(object):
    """
    This is the object you use to interact with the database.
    """
    def close(self):
        """
        Closing a cursor just exhausts all remaining data.
        """
    def setinputsizes(self, *args):
        """Does nothing, required by DB API."""

    def setoutputsizes(self, *args):
        """Does nothing, required by DB API."""        
    def execute(self, query, args=None):
        """Execute a query

        :param str query: Query to execute.

        :param args: parameters used with query. (optional)
        :type args: tuple, list or dict

        :return: Number of affected rows
        :rtype: int

        If args is a list or tuple, %s can be used as a placeholder in the query.
        If args is a dict, %(name)s can be used as a placeholder in the query.
        """
    def executemany(self, query, args):
        # type: (str, list) -> int
        """Run several data against one query

        :param query: query to execute on server
        :param args:  Sequence of sequences or mappings.  It is used as parameter.
        :return: Number of rows affected, if any.

        This method improves performance on multiple-row INSERT and
        REPLACE. Otherwise it is equivalent to looping over args with
        execute().
        """
    def fetchone(self):
        """Fetch the next row"""
    def fetchmany(self, size=None):
        """Fetch several rows"""
    def fetchall(self):
        """Fetch all the rows"""

两种询问函数

这两个玩意的区别, 详见这个博客:

https://blog.csdn.net/qq_41017902/article/details/107765724

后头的args用于参数化查询语句, 但是现在可以使用format的情况下直接format更方便一点

另外, 在数据量大的时候, 使用executemany能有更好的性能
三种数据返回函数

第一种是单条返回, 而后游标移动到下一个位置

第二种是指定返回的数据条数

第三种是全部返回
一个清理函数

执行清理后cursor被关闭

获取数据

import pymysql

if __name__ == "__main__":
    # 确定连接参数
    DB_URL = "localhost"
    DB_NAME = "employee"
    DB_USERNAME = "root"
    DB_PASSWORD = "1234"
    # 连接数据库
    db = pymysql.connect(DB_URL, DB_USERNAME, DB_PASSWORD, DB_NAME)
    # 创建游标对象
    cursor01 = db.cursor()
    # 使用游标对象执行SQL语句
    cursor01.execute("SELECT * FROM employee_table_1")
    # 获取返回的数据
    data = cursor01.fetchall()
    # 逐条打印数据
    for tmp in data:
        print(tmp)
    print("--------------------------------")
    cursor01.execute("SELECT * FROM employee_table_1")
    data = cursor01.fetchmany(3)
    for tmp in data:
        print(tmp)
    print("--------------------------------")
    cursor01.execute("SELECT * FROM employee_table_1")
    data = cursor01.fetchone()
    print(data)
    # 清理
    cursor01.close()
    db.close()

输出:

(29, '奥观海', '1234', 27, '18821756685')
(30, '川建国', '5678', 28, '13121754539')
(31, '张浩', '9101122', 24, '18821759524')
(32, '张宇歌', '131415', 25, '13121752150')
--------------------------------
(29, '奥观海', '1234', 27, '18821756685')
(30, '川建国', '5678', 28, '13121754539')
(31, '张浩', '9101122', 24, '18821759524')
--------------------------------
(29, '奥观海', '1234', 27, '18821756685')

增删改数据:

增删改数据可直接使用cursor的execute()执行更新SQL语句, 但是要在后头多一个提交commit()

import pymysql

if __name__ == "__main__":
    # 确定连接参数
    DB_URL = "localhost"
    DB_NAME = "employee"
    DB_USERNAME = "root"
    DB_PASSWORD = "1234"
    # 连接数据库
    db = pymysql.connect(DB_URL, DB_USERNAME, DB_PASSWORD, DB_NAME)
    # 创建游标对象
    cursor01 = db.cursor()
    # 使用游标对象执行SQL语句
    cursor01.execute("SELECT * FROM employee_table_1;")
    # 获取返回的数据
    data = cursor01.fetchall()
    # 逐条打印数据
    for tmp in data:
        print(tmp)
        
        
    print("--------------------------------")
    # 添加
    newName = 'WDNMD'
    newPwd = '7355608'
    newAge = '20'
    newTel = '00000012345'
    sql = """INSERT INTO employee_table_1 (NAME, pwd, age, tel) VALUES('{}','{}','{}','{}');""".format(
        newName, newPwd, newAge, newTel)

    cursor01.execute(sql)
    db.commit()

    cursor01.execute("SELECT * FROM employee_table_1")
    data = cursor01.fetchall()
    # data = cursor01.fetchmany(3)
    for tmp in data:
        print(tmp)
        
        
    print("--------------------------------")
    # 删除
    sql = """DELETE FROM employee_table_1  WHERE {} = "{}" """.format(
        "NAME", "WDNMD")
    cursor01.execute(sql)
    db.commit()

    cursor01.execute("SELECT * FROM employee_table_1")
    data = cursor01.fetchall()
    # data = cursor01.fetchmany(3)
    for tmp in data:
        print(tmp)
        
        
    print("--------------------------------")
    # 更新
    sql = """UPDATE employee_table_1  SET {}="{}" WHERE {}="{}" """.format(
        "pwd",
        "7355608",
        "NAME",
        "奥观海",)
    cursor01.execute(sql)
    db.commit()

    cursor01.execute("SELECT * FROM employee_table_1")
    data = cursor01.fetchall()
    for tmp in data:
        print(tmp)

    # cursor01.execute("SELECT * FROM employee_table_1")
    # data = cursor01.fetchone()
    # print(data)
    # 清理
    cursor01.close()
    db.close()

输出:

(29, '奥观海', '1234', 27, '18821756685')
(30, '川建国', '5678', 28, '13121754539')
(31, '张浩', '9101122', 24, '18821759524')
(32, '张宇歌', '131415', 25, '13121752150')
--------------------------------
(29, '奥观海', '1234', 27, '18821756685')
(30, '川建国', '5678', 28, '13121754539')
(31, '张浩', '9101122', 24, '18821759524')
(32, '张宇歌', '131415', 25, '13121752150')
(45, 'WDNMD', '7355608', 20, '00000012345')
--------------------------------
(29, '奥观海', '1234', 27, '18821756685')
(30, '川建国', '5678', 28, '13121754539')
(31, '张浩', '9101122', 24, '18821759524')
(32, '张宇歌', '131415', 25, '13121752150')
--------------------------------
(29, '奥观海', '7355608', 27, '18821756685')
(30, '川建国', '5678', 28, '13121754539')
(31, '张浩', '9101122', 24, '18821759524')
(32, '张宇歌', '131415', 25, '13121752150')

清理:

记得在完成查询操作后即时关闭这俩

先创建的后关闭

# 清理
cursor01.close()
db.close()

异常 & 错误处理:

这个后头在看

DB API 中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常:

异常	描述
Warning	当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类。
Error	警告以外所有其他错误类。必须是 StandardError 的子类。
InterfaceError	当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。必须是 Error 的子类。
DatabaseError	和数据库有关的错误发生时触发。必须是 Error 的子类。
DataError	当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。必须是 DatabaseError 的子类。
OperationalError	指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。必须是 DatabaseError 的子类。
IntegrityError	完整性相关的错误，例如外键检查失败等。必须是 DatabaseError 子类。
InternalError	数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。必须是 DatabaseError 子类。
ProgrammingError	程序错误，例如数据表（table）没找到或已存在、SQL 语句语法错误、参数数量错误等等。必须是 DatabaseError 的子类。
NotSupportedError	不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上使用 .rollback() 函数，然而数据库并不支持事务或者事务已关闭。必须是 DatabaseError 的子类。

Janus_V

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python Mysql数据库连接

MySQL数据库连接:参考博客:https://www.cnblogs.com/liubinsh/p/7568423.html这里使用pymysql包, 需要使用pip预先安装, 安装过程就不阐述了基本的流程与JDBC相似栗子:import pymysqlif __name__ == "__main__": # 确定连接参数 DB_URL = "localhost" DB_NAME = "employee" DB_USERNAME = "root"
复制链接

扫一扫