BCP Export sql data to csv

[mg24]

Hi Team,

I found below code on google, while searching for BCP Export with header.

-h-1 ------ this removes header as per their explanation, But I want to keep it into output file.


Also is this possible run query using Subprocess module or BCP Module

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700
-h-1 removes column name headers from the result

-s"," sets the column seperator to ,

-w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)

[Larz60+]

You can do this with python, but what you have above is a Microsoft sqlcmd command for SQL server, and isn't python code.
In python, this can be done using pandas.
see pandas.DataFrame.to_csv

[Pedroski55]

In MySQL you can get the column names like this:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database_name' AND TABLE_NAME = 'my_table_name';

Not sure why you would do that, you must know the names of the columns in a table in order to extract information.

The simple tables I have all start life as spreadsheet on my laptop.

You can put the above in Python like this:

import pymysql

def mysqlGet_Column_names(database, table): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.123', 
        user='me',  
        password = 'top_secret', 
        db='allstudentsdb', 
        ) 
      
    cur = conn.cursor()

    # Select query
    
    # sql = f'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = \'{database}\'  AND TABLE_NAME = \'{table}\';'
    sql = f'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s  AND TABLE_NAME = %s;'
    cur.execute(sql, (database, table, ))
    output = cur.fetchall()           
    # To close the connection 
    conn.close()
    return output

# columns is a tuple of tuples
columns = mysqlRemoteCW('allstudentsdb', 'allstudentsBE')

Just put the content of columns in your csv as the first line!