0

I want to export the model data(in bytea format stored in psql database) into a csv and then create a dataframe using pandas.

My code which I tried:

query = ("""SELECT  model from {} where col1 = '{}' and col2 = '{}'""").format(
        os.environ['table'], os.environ['col2'], hier)

    with tempfile.TemporaryFile() as tmpFile:
        copy_sql = "COPY ({query}) TO STDOUT WITH CSV {head}".format(
            query=query, head="HEADER"
        )
        conn = engine.raw_connection()
        cur = conn.cursor()
        cur.copy_expert(copy_sql, tmpFile)
        tmpFile.seek(0)
        dataFrame = pd.read_csv(tmpFile)

It dataFrame['model'] gives data in string and not bytes-like object format.

Update: Using pd.read_sql command ;

dataFrame = pd.read_sql(query, con=engine, columns=['col1', 'model'])
Rohan Kumar
  • 181
  • 1
  • 2
  • 16
  • 1
    Why do you want to go through CSV instead of creating the dataframe directly from SQL data? – Błotosmętek Apr 13 '20 at 21:01
  • @Błotosmętek yeah, I tried pd.read_sql, but I came to knw that pd.read_sql is slower than going through the pd.read_csv command – Rohan Kumar Apr 13 '20 at 21:03
  • This sounds quite unlikely; how exactly were you using `read_sql`? Also, what use is a faster method if it doesn't produce correct result? – Błotosmętek Apr 13 '20 at 21:21
  • @Błotosmętek I have updated the quesn with the read_sql command , Can you suggest a different method apart from read_sql to create a df of binary data – Rohan Kumar Apr 13 '20 at 21:25
  • @Błotosmętek https://stackoverflow.com/a/50809892/10861949 this link explains the thinking behind using read_csv method – Rohan Kumar Apr 13 '20 at 21:28
  • I am still not completely convinced this is worth the effort, but, if you insist, you could probably go with your approach and fix the binary data by applying `str.encode` to the appriopriate columns in the resulting dataframe. Also be warned that PostgreSQL 9.x exports bytea encoded in hex: https://stackoverflow.com/questions/6730729/how-to-download-postgres-bytea-column-as-file – Błotosmętek Apr 14 '20 at 09:32

0 Answers0