Total Beginner - Extracting Data from a Database Online (Screenshot)

[logan.c...@gmail.com]

Hey guys,

I'm learning Python and I'm experimenting with different projects -- I like learning by doing. I'm wondering if you can help me here:

http://i.imgur.com/KgvSKWk.jpg

What this is is a publicly-accessible webpage that's a simple database of people who have used the website. Ideally what I'd like to end up with is an excel spreadsheet with data from the columns #fb, # vids, fb sent?, # email tm.

I'd like to use Python to do it -- crawl the page and extract the data in a usable way.

I'd love your input! I'm just a learner.

[Dave Angel]

On 05/24/2013 01:32 PM, logan.c...@gmail.com wrote:
> Hey guys,
>
> I'm learning Python

Welcome.

> and I'm experimenting with different projects -- I like learning by doing. I'm wondering if you can help me here:
>

>na

>
> What this is is a publicly-accessible webpage

No, it's just a jpeg file, an image.

> that's a simple database of people who have used the website. Ideally what I'd like to end up with is an excel spreadsheet with data from the columns #fb, # vids, fb sent?, # email tm.
>
> I'd like to use Python to do it -- crawl the page and extract the data in a usable way.
>

But there's no page to crawl. You may have to start by finding an ocr
to interpret the image as characters. Or find some other source for
your data.

> I'd love your input! I'm just a learner.
>

--
DaveA

[Carlos Nepomuceno]

### table_data_extraction.py ###
# Usage: table[id][row][column]
# tables[0]       : 1st table
# tables[1][2]    : 3rd row of 2nd table
# tables[3][4][5] : cell content of 6th column of 5th row of 4th table
# len(table)      : quantity of tables
# len(table[6])   : quantity of rows of 7th table
# len(table[7][8]): quantity of columns of 9th row of 8th table

impor re
import urllib2

#to retrieve the contents of the page
page = urllib2.urlopen("http://example.com/page.html").read().strip()

#to create the tables list
tables=[[re.findall('<TD>(.*?)</TD>',r,re.S) for r in re.findall('<TR>(.*?)</TR>',t,re.S)] for t in re.findall('<TABLE>(.*?)</TABLE>',page,re.S)]

Pretty simple. Good luck!

----------------------------------------
> Date: Fri, 24 May 2013 10:32:26 -0700
> Subject: Total Beginner - Extracting Data from a Database Online (Screenshot)
> From: logan.c...@gmail.com
> To: pytho...@python.org

> --
> http://mail.python.org/mailman/listinfo/python-list

[Dave Angel]

On 05/24/2013 07:36 PM, Carlos Nepomuceno wrote:
>
> <SNIP>

> page = urllib2.urlopen("http://example.com/page.html").read().strip()
>
> #to create the tables list
> tables=[[re.findall('<TD>(.*?)</TD>',r,re.S) for r in re.findall('<TR>(.*?)</TR>',t,re.S)] for t in re.findall('<TABLE>(.*?)</TABLE>',page,re.S)]
>
>
> Pretty simple. Good luck!

Only if the page is html, which the OP's was not. It was an image. Try
parsing that with regex.



--
DaveA

[Chris Angelico]

On Sat, May 25, 2013 at 3:32 AM, <logan.c...@gmail.com> wrote:
> http://i.imgur.com/KgvSKWk.jpg
>

> What this is is a publicly-accessible webpage...

If that's a screenshot of something that we'd be able to access
directly, then why not just post a link to the actual thing? More
likely I'm thinking it's NOT publicly accessible, which is why it's
been censored.

ChrisA

[neil.s...@gmail.com]

If you are talking about accessing a web page, rather than an image, then what you want to do is known as 'screen scraping'.

One of the best tools for this is called BeautifulSoup.

http://www.crummy.com/software/BeautifulSoup/

[logan.c...@gmail.com]

Sorry to be unclear -- it's a screenshot of the webpage, which is publicly accessible, but it contains sensitive information. A bad combination, admittedly, and something that'll be soon fixed.

[John Ladasky]

On Friday, May 24, 2013 4:36:35 PM UTC-7, Carlos Nepomuceno wrote:
> #to create the tables list
> tables=[[re.findall('<TD>(.*?)</TD>',r,re.S) for r in re.findall('<TR>(.*?)</TR>',t,re.S)] for t in re.findall('<TABLE>(.*?)</TABLE>',page,re.S)]
>
>
> Pretty simple.

Two nested list comprehensions, with regex pattern matching?

Logan did say he was a "total beginner." :^)

[logan.c...@gmail.com]

Oh goodness, yes, I have no clue.

[Carlos Nepomuceno]

----------------------------------------
> Date: Mon, 27 May 2013 17:58:00 -0700
> Subject: Re: Total Beginner - Extracting Data from a Database Online (Screenshot)
> From: logan.c...@gmail.com
> To: pytho...@python.org
[...]

>
> Oh goodness, yes, I have no clue.

For example:

# to retrieve the contents of all column '# fb' (11th column from the image you sent)

c11 = [tables[0][r][10] for r in range(len(tables[0]))]
#      ----------------                -------------
#      this is the content             this is the quantity
#      of the 11th cell                of rows in table[0]
#      of row 'r'

[Phil Connell]

On 28 May 2013 02:21, "Carlos Nepomuceno" <carlosne...@outlook.com> wrote:
>
> ----------------------------------------
> > Date: Mon, 27 May 2013 17:58:00 -0700

> > Subject: Re: Total Beginner - Extracting Data from a Database Online (Screenshot)
> > From: logan.c...@gmail.com
> > To: pytho...@python.org
> [...]

> >
> > Oh goodness, yes, I have no clue.
>

> For example:
>
> # to retrieve the contents of all column '# fb' (11th column from the image you sent)
>
> c11 = [tables[0][r][10] for r in range(len(tables[0]))]

Or rather:

c11 = [row[10] for row in tables[0]]

In most cases, range(len(x)) is a sign that you're doing it wrong :)