Odoo Help

1

Trying to extract a pdf file from ir_attachment.dbdatas

By
Cameron
on 8/18/16, 8:49 AM 608 views

Hi - we have a crashed Openerp 7 db adn where trying to extract a pdf file from ir_attachment.dbdatas

I have tried directly via SQL but although I can retrieve something it appears to be "Gobbledygook!!

Is there a way of extracting files from the db?

copy 
(SELECT 
db_datas
FROM
ir_attachment
WHERE
name='Invoice_SAJ_2016_0964_.pdf')
to '/tmp/Invoice.pdf' (FORMAT "binary");

Any advice please.

If I open each file in a text editor each file starts with "PGCOPY" - a clue maybe?

Hi Cameron,

Querying the database will not work because the pdf file is encoded by openerp. You have to use some python code to extract the pdf file:

Please fix the python code accordingly. I dunno how to paste and format a code block in this forum. :)

Formated Code: http://paste.ofcode.org/hecdAnzV2xKXnL238r8Scm

import psycopg2

import base64

def main():

# Try to connect

try:

conn=psycopg2.connect("dbname='yourdatabase' user='openerp' password='openerp'")

except:

print "unable to connect to database."

cur = conn.cursor()

try:

cur.execute("""select

a.name

, a.create_date

, regexp_replace(a.description , E'[\\n\\r]+', ' ', 'g' ) description

, a.datas_fname

, a.id

, a.db_datas --binary attachment encoded

from ir_attachment a

where a.datas_fname is not null

order by id

limit 10;

""")

except:

print "Error querying Postgresql"

rows = cur.fetchall()

try:

for row in rows:

print row[0] # name

print row[1] # description

print row[2] # datas_fname

print row[3]

#save pdf to disk

#invalid pdf. It's encoded in base64 by openerp

f = open('c:/Temp/file.pdf', 'wb')

f.write(row[5])

f.close()

#valid pdf.

f= open('c:/Temp/file_decode.pdf', 'wb')

#we have to decode first

f.write(base64.b64decode(row[5]))

f.close()

except ValueError:

print ValueError

if __name__ == '__main__':

main()

Greetings.

Jaime Vasquez
on 8/21/16, 1:37 AM

Jamie - :) I cannot thank you enough. Worked a treat. Thank you, thank you, thank you :)

Cameron
on 8/22/16, 11:49 AM
1

Cameron

--Cameron--
1087
| 6 5 8
United Kingdom
--Cameron--
Cameron
On 8/23/16, 5:47 AM

This is my reHASH of Sir 'Jaime Vasquez' above code, I tried converting his comment to an answer but kept failing.

Jaime, please forgive my poor rehash of your coding, I'm still in the "Jowels flapping in the wind, vertical acceleration learning phase" :)


import psycopg2

import base64

def main():

#Dataase details

host='192.168.0.100'

port= '5432'

dbname='CrashedDbName'

user='openerp'

password='LittlePigLetMeIn'

#Database connection

try:

conn = psycopg2.connect ("host='"+host+"' port= '"+port+"' dbname= '"+dbname+"' user= '"+user+"' password= '"+password+"'")

cur = conn.cursor()

print "Connected"

except:

print "Unable to connect to database."

return

try:

#Invoice range to extract

for Invoice in range (768,967):

SearchString = "SELECT a.name, description, a.create_date, a.datas_fname, a.id, a.db_datas FROM ir_attachment a WHERE a.datas_fname = '"+"INVSAJ20160"+str(Invoice)+".pdf.pdf"+"';"

cur.execute(SearchString)

rows = cur.fetchall()

#Save Invocies to file

for row in rows:

if row[5]:

#valid pdf

#Save path

f= open('C:\Users\Me\Documents\Invoices\INVSAJ20160'+str(Invoice)+'.pdf', 'wb')

#We have to decode first

f.write(base64.b64decode(row[5]))

f.close()

except:

print ValueError

return

if __name__ == '__main__':

main()

About This Community

This platform is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

Register

Odoo Training Center

Access to our E-learning platform and experience all Odoo Apps through learning videos, exercises and Quizz.

Test it now

Question tools

3 follower(s)

Stats

Asked: 8/18/16, 8:49 AM
Seen: 608 times
Last updated: 1/18/17, 6:42 AM