SQL SERVER

Query all tables in the database

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

import pyodbc

# Create the connection
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'
)

# Create the cursor
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']

Save all data in the SQL SERVER database to 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: 无
    """
    # Establish the connection
    conn = create_conn()
    cursor = conn.cursor()

    # Query all tables
    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:
        # Query the table header
        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)

        # Query the data
        sql = "select * from CLTV.%s;" % t
        result = query_data(cursor, sql)
        result = np.array(result)
        print(result)

        # Save to a temporary spreadsheet
        result = pd.DataFrame(result, columns=title_list)
        print(result)
        result.to_csv('%s.csv' % t, index=False)

    # Close the cursor and the connection
    cursor.close()
    conn.close()

if __name__ == '__main__':
    save_to_csv()