PANDAS .to_csv or .to_excel without specifying a file path

Question

I have written a simple script that logs into my company's SQL Server, executes a query, uses pandas to read the results into a variable, then calls the .to_csv function and finally attaches that file to an outlook email (using win32com.client) to send off to a specified end user(s).

Currently the script works, but when I call .to_csv, I have to specify a file location, save the file to that location, then reference that file path when creating the outlook attachment. I'm looking to eliminate that step by creating the .csv and attaching to an email without physically saving the file somewhere. Is this possible?

Here is some sample code of what I have:

import pyodbc
import pandas as pd
import os
from datetime import date
import win32com.client as win32

cnxn_str = () #cnxn string detail)
cnxn = pyodbc.connect(cnxn_str)

query = '''
#QUERY GOES HERE
        '''

data = pd.read_sql(query, cnxn)

del cnxn

path = (file path) **<-- this is what I want to eliminate**
data.to_csv(os.path.join(path,f'{date.today()}_data.csv'),encoding='utf-8',index=False)** <-- can I call .to_csv without a         path?

**

outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
mail.To = ''#recipient
mail.Subject = ''
mail.Body = 'Hey Matthew, attached is a current Open Orders query.'

# To attach a file to the email (optional):
attachment  = os.path.join(path,f'{date.today()}_data.csv') **<-- how can I create .csv and attach to email in one step     without saving the file somewhere?**

mail.Attachments.Add(attachment)


mail.Send()

Answer 1

No, not in Outlook Object Model - Attachments.Add takes either a fully qualified file name or another Outlook item. The native Outlook API (Extended MAPI) knowns nothing about files (it works with IStream COM interface for the attachment data), but to use it you'd need either C++ or Delphi or a MAPI wrapper like Redemption (I am its author).

Answer 2

No, that is not possible when automating Outlook. The Outlook object model like any other MS Office applications is designed to deal with local files only. The Add method of the Attachments class creates a new attachment in the Attachments collection. The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment.

Only the low-level API (Extended MAPI) allows dealing with streams and set up attachments content on the fly without touching the hard drive.