How to select NULL and blank values from MySQL table into csv

[python300]

Hi all,

I am trying to select specific columns from a MySQL table into csv.
This table can have NULL or blank values and I want to differentiate between two when I extract.
Right now what's happening is that the extract has blank values for both NULL and blank values.

Is there any recommended way to do this?

import mysql.connector

mydb = mysql.connector.connect(
    host="host",
    user="user",
    password="pwd",
)

mycursor = mydb.cursor()

mycursor.execute("select * from db_name.table_name")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

Somehow formatting is not working for me so can't conver code into code snippet or insert images but trying my best.
The above is printing both blank and NULL values same: blank
File produced is this way, no diff between the NULL and Blanks values:
   
|2020-09-18 02:48:04|||||||||

[deanhystad]

Wrap code in python tags to save formatting

Python csv.writer will write null as a blank string. Actually, it writes None as a blank string. Database nulls are converted to Python Nones.

Knowing that you cannot write a None (or a null) to a csv file, what do you want to write? Maybe replace all occurrences of None in myresult to the string "null"?

[python300]

Wrap code in python tags to save formatting

Python csv.writer will write null as a blank string. Actually, it writes None as a blank string. Database nulls are converted to Python Nones.

Knowing that you cannot write a None (or a null) to a csv file, what do you want to write? Maybe replace all occurrences of None in myresult to the string "null"?

Thank you for your reply. Yes I would try to get something other than blank for DB NULL value. I am not sure what will be best but I think using NaN might be a good idea. I will use these values to load into oracle DB and there also I want to be able to insert DB NULL or blank to match what was in the MySQL DB. Using NaN might help to insert NULL using pandas and numpy.

[deanhystad]

NaN will just be a string. Whatever usesthe generated csv file will have to know that the string "NaN" means NaN. What you want to write to the csv file in place of nulls will depend on what is using the generated csv file.

[python300]

NaN will just be a string. Whatever usesthe generated csv file will have to know that the string "NaN" means NaN. What you want to write to the csv file in place of nulls will depend on what is using the generated csv file.

Unfortunately I found out the process that'll use generated csv file needs everything (including empty strings) in quotes except DB NULL values. So for DB values:
ABC, 123, ,NULL,2022-12-22
The csv should have
"ABC"|"123"|""||"2022-12-22"

Is there any way to do this using Python without having to do row level operations?
I am trying to use Pandas and csv modules but couldn't find a way to represent DB NULL with nothing and empty string with "".
In csv I get either
"ABC"|"123"|""|""|"2022-12-22"

#df.to_csv(r'file_name.csv', index=False, sep='|', quoting=csv.QUOTE_ALL, na_rep=None)

OR
"ABC"|123|""|""|"2022-12-22"

#df.to_csv(r'file_name.csv', index=False, sep='|', quoting=csv.QUOTE_NONNUMERIC, na_rep=None)

OR
ABC|123|||2022-12-22

#df.to_csv(r'file_name.csv', index=False, sep='|', quoting=csv.QUOTE_MINIMAL, na_rep=None)

My complete code is

import mysql.connector
import pandas as pd
import csv

mydb = mysql.connector.connect (
    host = "hostname",
    user = "user_name",
    password = "pwd",
    database = "db_name"
)

sqlquery = pd.read_sql_query('''select * from db_name.table_name''')

df = pd.DataFrame(sqlquery)
df.to_csv(r'file_name.csv', index=False, sep='|', quoting=csv.QUOTE_ALL, na_rep=None)

[deanhystad]

Looks like you'll have to write the file yourself.

import pandas as pd
 
odd_stuff = pd.DataFrame([[1, '1', 'one', None, ''], [2, '2', '', None, None]])
 
def df_to_csv(dataframe, csv_file, sep=','):
    """Write dataframe to csv file using special conventions.
    1: All values except None are surrounded by double quotes.
    2: None will appear as a blank.
    """
    def rowstr(row):
        """Format row as a str for writing to a csv file"""
        return sep.join(['' if x is None else f'"{x}"' for x in row])
 
df_to_csv(odd_stuff, "test.csv", sep='|')

test.csv

Output:
"1"|"1"|"one"||""
"2"|"2"|""||

[python300]

Looks like you'll have to write the file yourself.

import pandas as pd

odd_stuff = pd.DataFrame([[1, '1', 'one', None, ''], [2, '2', '', None, None]])

def df_to_csv(dataframe, csv_file, sep=','):
    """Write dataframe to csv file using special conventions.
    1: All values except None are surrounded by double quotes.
    2: None will appear as a blank.
    """
    def rowstr(row):
        """Format row as a str for writing to a csv file"""
        return sep.join(['' if x is None else f'"{x}"' for x in row])

df_to_csv(odd_stuff, "test.csv", sep='|')

test.csv

Output:
"1"|"1"|"one"||""
"2"|"2"|""||

Thank you again deanhystad but is this going to to replace None with '' row by row? It seems like that to me but just trying to be sure as I am little confused with use of "row"

[deanhystad]

I really messed up my previous post during an edit. No wonder you couldn't figure out what it was supposed to do.
Sorry about that. The code should look like this:

import pandas as pd

odd_stuff = pd.DataFrame([[1, '1', 'one', None, ''], [2, '2', '', None, None]])

def df_to_csv(dataframe, csv_file, sep=','):
    """Write dataframe to csv file using special conventions.
    1: All values except None are surrounded by double quotes.
    2: None will appear as a blank.
    """
    with open(csv_file, "w") as file:
        for row in dataframe.values:
            row_str = sep.join(['' if x is None else f'"{x}"' for x in row])
            print(row_str, file=file)

df_to_csv(odd_stuff, "test.csv", sep='|')

First it creates a list of str objects from the objects in a row. None becomes an empty str and anything else becomes str(obj) wrapped in double quotes. Next I join all the str objects together separated by sep.

Hope this is clear now.

[python300]

This works great but I was trying to avoid row level operations as the data could be huge. But I am evaluating your suggestion to see if it'll be feasible for my use case. Thank you again.

[deanhystad]

I don't think you have any choice. Your file format is odd enough that it is unlikely to be supported.