Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #109269 > unrolled thread

Best way to inplace alter a list going into postgres

Back to article view | Back to comp.lang.python

Contents

  Best way to inplace alter a list going into postgres Sayth Renshaw <flebber.crue@gmail.com> - 2016-05-30 21:27 -0700
    Re: Best way to inplace alter a list going into postgres Ben Finney <ben+python@benfinney.id.au> - 2016-05-31 14:54 +1000
      Re: Best way to inplace alter a list going into postgres Sayth Renshaw <flebber.crue@gmail.com> - 2016-05-30 22:42 -0700
        Re: Best way to inplace alter a list going into postgres Ben Finney <ben+python@benfinney.id.au> - 2016-05-31 15:52 +1000
    Re: Best way to inplace alter a list going into postgres Steven D'Aprano <steve+comp.lang.python@pearwood.info> - 2016-05-31 15:58 +1000
      Re: Best way to inplace alter a list going into postgres Sayth Renshaw <flebber.crue@gmail.com> - 2016-05-30 23:19 -0700
        Re: Best way to inplace alter a list going into postgres Sayth Renshaw <flebber.crue@gmail.com> - 2016-05-31 01:46 -0700
          Re: Best way to inplace alter a list going into postgres Ben Finney <ben+python@benfinney.id.au> - 2016-06-01 06:10 +1000

#109269 — Best way to inplace alter a list going into postgres

Hi

What is the best way to inplace alter a list going into a postgres database using split but being sure that the order of items remains correct.

I am using this list of ids to retrieve data from XML

horseattrs = ('id', 'race_id', 'horse', 'number', 'finished', 'age', 'sex',
              'blinkers', 'trainernumber', 'career', 'thistrack', 'firstup',
              'secondup', 'variedweight', 'weight', 'pricestarting')

one example output looks like this.

['188745', '215639', 'Beau Tirage', '2', '0', '4', 'G', '0', '211', '11-3-1-4 $72390.00', '2-0-0-2 $8970.00', '3-2-0-0 $30085.00', '3-1-0-0 $15450.00', '59', '59', '']

so if I need to manage to split say a[10] if I call the list a and break that into 4 columns. Maybe like this.

In [1]: a = ['188745', '215639', 'Beau Tirage', '2', '0', '4', 'G', '0', '211', '11-3-1-4 $72390.00', '2-0-0-2 $8970.00', '3-2-0-0 $30085.00', '3-1-0-0 $15450.00', '59', '59', '']

In [2]: print(a)
['188745', '215639', 'Beau Tirage', '2', '0', '4', 'G', '0', '211', '11-3-1-4 $72390.00', '2-0-0-2 $8970.00', '3-2-0-0 $30085.00', '3-1-0-0 $15450.00', '59', '59', '']

In [3]: print(a[10])
2-0-0-2 $8970.00

In [6]: b = print(a[10].replace('-',' '))
2 0 0 2 $8970.00

In [9]: print(b.split(' ',))
['2', '0', '0', '2', '$8970.00']



Currently using this as the insert 
conn = psycopg2.connect("")
with conn, conn.cursor() as cur:
        # First, create tables.
    cur.execute("drop table if exists meetings, races, horses")
    cur.execute("create table meetings (" +
                ", ".join("%s varchar" % fld for fld in meetattrs)
                + ")")
    cur.execute("create table races (" +
                ", ".join("%s varchar" % fld for fld in raceattrs)
                + ")")
    cur.execute("create table horses (" +
                ", ".join("%s varchar" % fld for fld in horseattrs)
                + ")")

    # Now walk the tree and insert data.
    for filename in sorted(file_list):
        for meeting in pq(filename=my_dir + filename):
            meetdata = [meeting.get(attr) for attr in meetattrs]
            cur.execute("insert into meetings values (" +
                        ",".join(["%s"] * len(meetattrs)) + ")", meetdata)
            for race in meeting.findall("race"):
                race.set("meeting_id", meeting.get("id"))
                racedata = [race.get(attr) for attr in raceattrs]
                cur.execute("insert into races values (" +
                            ",".join(["%s"] * len(raceattrs)) + ")", racedata)
                for horse in race.findall("nomination"):
                    horse.set("race_id", race.get("id"))
                    horsedata = [horse.get(attr) for attr in horseattrs]
                    print(horsedata)
                    cur.execute("insert into horses values (" +
                                ",".join(["%s"] * len(horseattrs)) + ")",
                                horsedata)


What is the best way to manage this.

Sayth

[toc] | [next] | [standalone]

#109270

Sayth Renshaw <flebber.crue@gmail.com> writes:

> What is the best way to inplace alter a list going into a postgres
> database using split but being sure that the order of items remains
> correct.

That's a trick question. The best way to modify fields of a record is
not with a list.

Instead, you should get the record in the form of a mapping (such as
Python's built-in mapping type, ‘dict’) and alter the fields *by name*.

Ony when it's time to serialise the data should you then convert it to a
list, extracting by the sequence of field names (‘horseattrs’ in your
example).

-- 
 \       “Firmness in decision is often merely a form of stupidity. It |
  `\        indicates an inability to think the same thing out twice.” |
_o__)                                                —Henry L. Mencken |
Ben Finney

[toc] | [prev] | [next] | [standalone]

#109271

Ah so I should create a function that processes and modifies in the middle of the process between obtaining and committing.

Or if I need to do this and more processing should I be using something like sqlalchemy or peewee http://docs.peewee-orm.com/en/latest/ ?

Sayth

[toc] | [prev] | [next] | [standalone]

#109272

Sayth Renshaw <flebber.crue@gmail.com> writes:

> Ah so I should create a function that processes and modifies in the
> middle of the process between obtaining and committing.

Separating the tasks:

* Get the data from the database, into a form useful inside your
  program. (For a Python program manipulating records from a database, a
  mapping is best because you'll be referring to fields by name.)

* Serialise the altered data back to the database. How you do this
  depends on what the program expects for a database API; my general
  recommendation is, don't re-invent the wheel.

> Or if I need to do this and more processing should I be using
> something like sqlalchemy or peewee
> http://docs.peewee-orm.com/en/latest/ ?

SQLAlchemy is certainly worth learning, so that you can decide when its
power and complexity are needed.

I'm not familiar with Peewee.

If you find that you are writing a lot of code just to handle the
interface between the database and the Python program, definitely use
SQLAlchemy or something like it, that's what they are for.

-- 
 \           “Value your freedom or you will lose it, teaches history. |
  `\     “Don't bother us with politics,” respond those who don't want |
_o__)                            to learn.” —Richard M. Stallman, 2002 |
Ben Finney

[toc] | [prev] | [next] | [standalone]

#109273

On Tuesday 31 May 2016 14:27, Sayth Renshaw wrote:

> 
> Hi
> 
> What is the best way to inplace alter a list going into a postgres database

Is it relevant where it is going?

What you do with the list after you alter it is irrelevant -- perhaps you will 
insert it into an Oracle database, or a place it in a dict, or print it. The 
only reason it might be relevant is if Postgres can do the work for you, so you 
don't need to split it in Python. And that's probably a question for a Postgres 
group, not a Python group.


> using split but being sure that the order of items remains correct.

To modify a list *in place* is tricky. You can use slicing, which is easy, but 
because the size of the list changes you need to work backwards from the end.

I will use 'x' for fields that don't change, and suppose you wish to split 
fields 9, 10, 11 and 12. (Remember that Python counts starting from 0, not 1.)

L = ['x', 'x', 'x', 'x', 'x', 'x', 'x', 'x', 'x', 
     '11-3-1-4 $72390.00',
     '2-0-0-2 $8970.00', 
     '3-2-0-0 $30085.00', 
     '3-1-0-0 $15450.00',
     'x', 'x', 'x'
     ]
for index in (12, 11, 10, 9):  # must process them in *reverse* order
    item = L[index]
    values = item.replace('-', ' ').split()
    L[index:index+1] = values  # slice assignment


Perhaps a better way is to build a new list:

L = ['x', 'x', 'x', 'x', 'x', 'x', 'x', 'x', 'x', '11-3-1-4 $72390.00',
     '2-0-0-2 $8970.00', '3-2-0-0 $30085.00', '3-1-0-0 $15450.00',
     'x', 'x', 'x']
new = []
for i, item in enumerate(L):
    if i in (9, 10, 11, 12):
        values = item.replace('-', ' ').split()
        new.extend(values)
    else:
        new.append(values)



You can then replace L with new *in-place* with a slice:

L[:] = new

or just use new instead.



-- 
Steve

[toc] | [prev] | [next] | [standalone]

#109274

On Tuesday, 31 May 2016 15:58:40 UTC+10, Steven D'Aprano  wrote:
> On Tuesday 31 May 2016 14:27, Sayth Renshaw wrote:
> 
> > 
> > Hi
> > 
> > What is the best way to inplace alter a list going into a postgres database
> 
> Is it relevant where it is going?
> 
> What you do with the list after you alter it is irrelevant -- perhaps you will 
> insert it into an Oracle database, or a place it in a dict, or print it. The 
> only reason it might be relevant is if Postgres can do the work for you, so you 
> don't need to split it in Python. And that's probably a question for a Postgres 
> group, not a Python group.
> 
According to this http://stackoverflow.com/a/29420035/461887

http://stackoverflow.com/a/8767450/461887

Postgres seems like it could perform it, I need to research further.

> 
> Perhaps a better way is to build a new list:
> 
> L = ['x', 'x', 'x', 'x', 'x', 'x', 'x', 'x', 'x', '11-3-1-4 $72390.00',
>      '2-0-0-2 $8970.00', '3-2-0-0 $30085.00', '3-1-0-0 $15450.00',
>      'x', 'x', 'x']
> new = []
> for i, item in enumerate(L):
>     if i in (9, 10, 11, 12):
>         values = item.replace('-', ' ').split()
>         new.extend(values)
>     else:
>         new.append(values)
> 
> 
> 
> You can then replace L with new *in-place* with a slice:
> 
> L[:] = new
> 
> or just use new instead.
> 
> 
> 
> -- 
> Steve

So with that cool enumerate example I need to decide whether its safer or more efficient to put the data directly into postgres and then modify it there with postgres methods or use python to modify prior to entry.

Need more research.

Thanks

Sayth

[toc] | [prev] | [next] | [standalone]

#109276

Probably easier to handle in postgres http://stackoverflow.com/a/37538641/461887

select c1[1] as col1, 
       c1[2] as col2, 
       c1[3] as col3,
       c1[4] as col4,
       substr(col5, 2) as col5
from (
   select string_to_array((string_to_array(the_column, ' '))[1], '-') as c1,
          (string_to_array(the_column, ' '))[2] as col5
   from the_table
) t

[toc] | [prev] | [next] | [standalone]

#109292

Sayth Renshaw <flebber.crue@gmail.com> writes:

> Probably easier to handle in postgres http://stackoverflow.com/a/37538641/461887

Yes, a proper RDBMS is expressly optimised for manipulating the data.

Especially when the problem at hand is expressible as a set operation,
the RDBMS is almost always the better place to do that.

This assumes you have an RDBMS that is good at manipulating data
efficiently (which rules out MySQL, but you've already done that I see.)

-- 
 \     “[…] we don’t understand what we mean when we say that [God] is |
  `\    ‘good’, ‘wise’, or ‘intelligent’.” —Karen Armstrong, _The Case |
_o__)                                                   For God_, 2009 |
Ben Finney

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web