Python库之pymssql连接MSSQL数据库并绘制直方图

逃离我推掉我的手 2024-02-20 11:22 50阅读 0赞

pymssql是一个Python的数据库接口，工作原理：  
使用connect创建连接对象；  
connect.cursor创建游标对象，SQL语句的执行在游标上执行；  
cursor.execute()方法执行SQL语句，cursor.fetch()方法获取查询结果；  
执行connect.commit()；  
调用close方法关闭游标cursor和数据库连接；  
一个连接一次只能有一个游标的查询处于活跃状态；  
可以通过使用with语句来省去显示的调用close方法关闭连接和游标；  
pymssql 2.0.0以上的版本可以通过cursor.callproc方法来调用存储过程；  
\# 数据存放到元组列表中，如果参数为as\_dict=True，返回字典；  
\# with conn.cursor() as cursor:  
\# with conn.cursor(as\_dict=True) as cursor:

import pymssql
    import matplotlib.pyplot as plt
    # import pandas as pd
    # import warnings
    # warnings.filterwarnings('ignore') #隐藏Pandas警告SQLAlchemy
    
    #创建连接字符串  （sqlserver默认端口为1433）
    conn =pymssql.connect(server="localhost",#本地服务器
                             port="1433",#TCP端口
                             user="sa",password="lqxxxx11",
                             database="tsl",
                             charset="utf8"
     
        )
    if conn:
       print('连接数据库成功!')#
    # 创建游标对象
    cursor = conn.cursor()
    # 编写SQL查询语句
    sql_query = "SELECT changeInto FROM cash123122 where paymentMethod LIKE N'%代发%'" 
    cursor.execute(sql_query)
    rows=cursor.fetchall()
    list2=[]
    for row in rows:
        list2.append(row[0])
        print(row[0])
    
    # 绘制第二个直方图
    plt.hist(list2, bins=60, alpha=0.5,rwidth=0.7)
    plt.xlabel('Value')
    plt.ylabel('Frequency')
    plt.show()
    
    # 关闭数据库连接
    conn.close()

import pymssql
    
    #创建连接字符串  （sqlserver默认端口为1433）
    conn =pymssql.connect(server="localhost",#本地服务器
                             port="1433",#TCP端口
                             user="sa",password="lqxxx1",
                             database="tsl",
                             charset="utf8"
     
        )
    if conn:
       print('连接数据库成功!')#测试是否连接上
    # 创建游标对象
    cursor = conn.cursor()
    
    # 执行SQL查询
    cursor.execute("SELECT * FROM Employees")
    
    # 获取查询结果
    result = cursor.fetchall()
    
    # 遍历结果
    for row in result:
        # print(row)
        #print("CustomerId: "+ row[0],"CustomerName: "+ row[1],"Email: "+row[2])
        print("CustomerId: %-6s CustomerName: %-10s Email: %-20s" % (row[0], row[1],row[2]))  
        
    
    # # 插入数据
    # insert_query = "INSERT INTO Employees (CustomerId,CustomerName,Email) VALUES (%s, %s, %s)"
    # insert_data = ('8829110', 'kliangzh','kliangzh@163.com')
    # cursor.execute(insert_query, insert_data)
    
    # 更新数据
    # update_query = "UPDATE Employees SET CustomerName = %s WHERE CustomerId = 8829110"
    # update_data = ('liangdd', )
    # cursor.execute(update_query, update_data)
    
    # 删除数据
    # delete_query = "DELETE FROM Employees WHERE CustomerName = %s"
    # delete_data = ("liangdd",)
    # cursor.execute(delete_query, delete_data)
    
    # 提交事务
    conn.commit()
    
    # 关闭游标
    cursor.close()

Basic features (strict DB-API compliance)

from os import getenv
    import pymssql
    
    server = getenv("PYMSSQL_TEST_SERVER")
    user = getenv("PYMSSQL_TEST_USERNAME")
    password = getenv("PYMSSQL_TEST_PASSWORD")
    
    conn = pymssql.connect(server, user, password, "tempdb")
    cursor = conn.cursor()
    cursor.execute("""
    IF OBJECT_ID('persons', 'U') IS NOT NULL
        DROP TABLE persons
    CREATE TABLE persons (
        id INT NOT NULL,
        name VARCHAR(100),
        salesrep VARCHAR(100),
        PRIMARY KEY(id)
    )
    """)
    cursor.executemany(
        "INSERT INTO persons VALUES (%d, %s, %s)",
        [(1, 'John Smith', 'John Doe'),
         (2, 'Jane Doe', 'Joe Dog'),
         (3, 'Mike T.', 'Sarah H.')])
    # you must call commit() to persist your data if you don't set autocommit to True
    conn.commit()
    
    cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
    row = cursor.fetchone()
    while row:
        print("ID=%d, Name=%s" % (row[0], row[1]))
        row = cursor.fetchone()
    
    conn.close()

[pymssql examples — pymssql 0.1.dev50+g55af2c7.d20231007 documentation][pymssql examples _ pymssql 0.1.dev50_g55af2c7.d20231007 documentation]

As of pymssql 2.0.0 **stored procedures** can be called using the rpc interface of db-lib如果要调用存储过程，则使用Cursor对象的callproc方法

with pymssql.connect(server, user, password, "tempdb") as conn:
        with conn.cursor(as_dict=True) as cursor:
            cursor.execute("""
            CREATE PROCEDURE FindPerson
                @name VARCHAR(100)
            AS BEGIN
                SELECT * FROM persons WHERE name = @name
            END
            """)
            cursor.callproc('FindPerson', ('Jane Doe',))
            # you must call commit() to persist your data if you don't set autocommit to True
            conn.commit()
            for row in cursor:
                print("ID=%d, Name=%s" % (row['id'], row['name']))

Rows can be fetched as dictionaries instead of tuples. This allows for accessing columns by name instead of index. Note the**`as_dict`** argument.如果指定了as\_dict为True，则返回结果变为字典类型，这样就能通过列名来访问结果了

conn = pymssql.connect(server, user, password, "tempdb")
    cursor = conn.cursor(as_dict=True)
    
    cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
    for row in cursor:
        print("ID=%d, Name=%s" % (row['id'], row['name']))
    
    conn.close()

You can use Python’s**`with`** statement with connections and cursors. This frees you from having to explicitly close cursors and connections.可以使用with语句来处理Connection和cursor对象，这样就不需要手动关闭他们了：

with pymssql.connect(server, user, password, "tempdb") as conn:
        with conn.cursor(as_dict=True) as cursor:
            cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
            for row in cursor:
                print("ID=%d, Name=%s" % (row['id'], row['name']))

[pymssql examples _ pymssql 0.1.dev50_g55af2c7.d20231007 documentation]: https://pymssql.readthedocs.io/en/latest/pymssql_examples.html