SQL SERVER

查询库中所有表

https://www.cnblogs.com/yujiaxiong/p/6664272.html

import pyodbc

# 创建连接
conn = pyodbc.connect(
    'DRIVER={SQL Server Native Client 11.0};'
    'SERVER=sephora-cn-udp.database.chinacloudapi.cn;'
    'DATABASE=CLV_POC_DataMart;'
    'UID=clv_majorel;'
    'PWD=Jvv01xbq7XRZ&xi6'
)

# 创建游标
cursor = conn.cursor()

sql1 = "select * from sysobjects where xtype='U';"
sql2 = "select * from sys.tables;"
sql3 = "exec sp_tables"
cursor.execute(sql3)
table_list = [i[2] for i in cursor.execute(sql3) if i[1] == 'CLTV']

把SQL SERVER数据库中所有的数据保存到csv中

import pyodbc
import pandas as pd
import numpy as np

def create_conn():
    """
    创建数据库连接
    :return: 连接游标
    """
    connection = pyodbc.connect(
        'DRIVER={SQL Server Native Client 11.0};'
        'SERVER=sephora-cn-udp.database.chinacloudapi.cn;'
        'DATABASE=CLV_POC_DataMart;'
        'UID=clv_majorel;'
        'PWD=Jvv01xbq7XRZ&xi6'
    )

    return connection

def query_data(c, s):
    """
    查询SQL SERVER数据库中的所有数据
    :param c: 连接状态
    :param s: 查询语句
    :return: 查询结果
    """
    c.execute(s)

    return c.fetchall()

def save_to_csv():
    """
    分别读取字段和数据，拼接成DataFrame格式，保存到表格
    :return: 无
    """
    # 建立连接
    conn = create_conn()
    cursor = conn.cursor()

    # 查所有表
    sql = "exec sp_tables"
    table_list = [i[2] for i in query_data(cursor, sql) if i[1] == 'CLTV']
    print(table_list)

    for t in table_list:
        # 查询表头
        sql = "SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('[CLTV].[%s]')" % t
        result = query_data(cursor, sql)
        title_list = [i[0] for i in result]
        print(title_list)

        # 查数据
        sql = "select * from CLTV.%s;" % t
        result = query_data(cursor, sql)
        result = np.array(result)
        print(result)

        # 保存到临时表格
        result = pd.DataFrame(result, columns=title_list)
        print(result)
        result.to_csv('%s.csv' % t, index=False)

    # 关闭游标和连接
    cursor.close()
    conn.close()

if __name__ == '__main__':
    save_to_csv()