[SQL] exporting Excel tables into PostgreSQL database with Python
Hi!
Here is a hopefully convenient
description of my situation:
- I have a main folder, containing
several subfolders.
- Every (sub)folder
contains one or more .xls - Workbooks.
- Every Workbook contains one or more
different Spreadsheets.
- The workbooks contain some cells which
have Hyperlink addresses to other,
relating workbooks.
- Some cells in wokrbooks contain
comments, which must also be exported.
My ultimate aim is to get ALL data, that means cell values, hyperlink
addresses in cells, and comments, into
corresponding PostgreSQL database
table. Principally there are at maximum three
different data per cell
(value,
hyperlink address, comment).
Here is my idea.,
which I lack of Python programming practice to implement
in a reasonable amount of time, so every
help is welcome:
Some kind of For - Loop, which looks into
all folders and subfolders. Then
opens all workbooks one after another. Then
looks into every spreadsheet of
a workbook. For every spreadsheet, read
out cell values, cell hyperlink
addresses (if there are any), and cell comments
(if there are any).
(Perhaps the win32com - module is
helpful?)
Then open a PostgreSQL database
connection, and insert the cell values into
a corresponding table.
(which should
already be created in the database before?)
This table should also contain a column
for the possible hyperlink
addresses, and possible cell comment strings.
I have enclosed various Code which might be of help:
- VBA Code samples for reading a cell
comment and a hyperlink address from a
spreadsheet cell.
- PostgreSQLConnection.py, a class to
connect to a PostgreSQL database
- Gerold.py, which uses the win32com -
module to access and manipulate an
Excel workbook.
My problem is how to combine these code
samples, especially implementing the
for-loop for going through the folders and
opening Excel
workbooks/spreadsheets, reading Excel data with win32com, and
creating/inserting this data in corresonding tables in the
database, using
pyPgSQL.
So, this sounds like fun work... any
help appreciated.
Cheers
Juergen
'
'Extract the text from a cell comment
'
Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean _
(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function
'
'Extract the underlying address from a cell containing a Hyperlink
'
Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:";, "")
End Function#!/usr/bin/env python
# -*- coding: iso-8859-1 -*-
import win32com.client
import win32ui
# Zum Excel verbinden
app = win32com.client.Dispatch("Excel.Application")
app.visible = True
# Neue Arbeitsmappe
workbook = app.Workbooks.Add()
# Zum Arbeitsblatt verbinden
sheet = workbook.Sheets[0]
# Kommentare hinzufügen
sheet.Range("A1").AddComment()
sheet.Range("A1").Comment.Text("Hallo")
sheet.Range("B2").AddComment()
sheet.Range("B2").Comment.Text("Welt")
# Kommentare auslesen und anzeigen
win32ui.MessageBox(
"Kommentar in A1: %s" % sheet.Range("A1").Comment.Text()
)
win32ui.MessageBox(
"Kommentar in B2: %s" % sheet.Range("B2").Comment.Text()
)
# Variablen löschen und damit die Verbindung zu Excel lösen
del sheet
del app
from pyPgSQL import PgSQL
class dbConn:
def __init__(self, **kwargs):
self.conn = None
self.conndata = kwargs
self.dbname = self.conndata['db']
self.PgSQL = PgSQL
self.connectPostGreSQL()
def connectPostGreSQL(self):
self.conn = self.PgSQL.connect( host=self.conndata['host'], \
database=self.conndata['db'], \
user=self.conndata['user'], \
password = self.conndata['passwd'])
self.cursor = self.conn.cursor()
# So und für die restliche SQL Syntax von Postgresql sollte man sich ein
Buch holen, da
# so Befehle wie concat, die man aus mySql her kennt, nicht funktionieren.
# Hier muss man den Befehl umbauen und Concatinieren mit || . Nur als ein
Beispiel
# von einigen.
newDB = dbConn(host="localhost", user="user1", passwd="", db="testDB")
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] lo function changed in PostgreSQL 8.1.1
Dear All, I use '$libdir/lo' for manage my Large Object for PostgreSQL 8.0.4 . Now I try to up my PostgreSQL to new version as 8.1.1 but I think I got some error about lo (Large Object). lo (Large Object) function that normally shown in function list now disappear. I'm sure that I already check on Large Object box when I install. I found some information on http://www.postgresql.org/docs/8.1/interactive/lo-interfaces.html#AEN26978 about lo function. PostgreSQL has many new lo function that I never use in version 8.0.4 and some function I have use is not found (lo_in , lo_out). Could you please advise me how to manage lo (Large Object) in PostgreSQL 8.1.1? (Normally I use VB6 as develop tool.) And I'm not sure about my old lo (Large Object) data. How can I restore it for use in PostgreSQL 8.1.1? Thank you
Re: [SQL] lo function changed in PostgreSQL 8.1.1
"Premsun Choltanwanich" <[EMAIL PROTECTED]> writes: > lo (Large Object) function that normally shown in function list now = > disappear. I'm sure that I already check on Large Object box when I = > install. I found some information on http://www.postgresql.org/docs/8.1/int= > eractive/lo-interfaces.html#AEN26978 about lo function. PostgreSQL has = > many new lo function that I never use in version 8.0.4 and some function I = > have use is not found (lo_in , lo_out). lo_in/lo_out aren't needed anymore because lo isn't a separate type, just a domain over OID. Why were you using them directly, anyway? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] DB design and foreign keys
hello all, i'm usign PostgreSQL 7.4.7 in a Debian 3.1 following is the SQL schema of my (very)small DB for a (very small)web business application: -- SQL schema for business-test-db CREATE TABLE customers ( customer_code serial, alfa_customer_code varchar(6), customer_name character varying(250) NOT NULL, address character varying(250) NOT NULL, city character varying(250) NOT NULL, zip_code character varying(8) NOT NULL, prov character varying(30) NOT NULL, security character varying(15) NOT NULL, tel character varying(30), tel2 character varying(20) NOT NULL, fax character varying(250), url character varying(250), email1 character varying(250) NOT NULL, email2 character varying(250) NOT NULL, discount1 integer, discount2 integer, PRIMARY KEY (customer_code) ); CREATE TABLE users ( id smallint NOT NULL, login varchar(20) NOT NULL, pwd varchar(20) NOT NULL, name varchar(20) NOT NULL, customer_code int REFERENCES customers (customer_code), valid date, primary key (id) ); CREATE TABLE products ( id serial, code varchar(60) UNIQUE NOT NULL, description varchar(250) NOT NULL, dimensions varchar(250) NOT NULL, price numeric NOT NULL, state boolean, PRIMARY KEY (id) ); CREATE TABLE orders ( id serial, order_code serial, customer_code integer REFERENCES customers (customer_code) NOT NULL, order_date time without time zone NOT NULL, remote_ip inet NOT NULL, order_time timestamp with time zone NOT NULL, order_type varchar(10) NOT NULL, state varchar(10) NOT NULL, PRIMARY KEY (id, order_code) ); CREATE TABLE order_items ( id serial, order_code integer REFERENCES orders (order_code) NOT NULL, customer_code integer REFERENCES customers (customer_code) NOT NULL, product_code varchar(60) REFERENCES products (code) NOT NULL, qty int NOT NULL, price numeric REFERENCES products (price) NOT NULL, row_price numeric, PRIMARY KEY (id, order_code) ); -- -- END OF FILE the tables: customers, users, products and orders are created as the SQL states. when i try to create the table order_items postgresql gives the following error: business-test-db=# CREATE TABLE order_items ( business-test-db(#id serial, business-test-db(#order_code integer REFERENCES orders (order_code) NOT NULL, business-test-db(#customer_code integer REFERENCES customers (customer_code) NOT NULL, business-test-db(#product_code varchar(60) REFERENCES products (code) NOT NULL, business-test-db(#qty int NOT NULL, business-test-db(#price numeric REFERENCES products (price) NOT NULL, business-test-db(#row_price numeric, business-test-db(#PRIMARY KEY (id, order_code) business-test-db(# ); NOTICE: CREATE TABLE will create implicit sequence "order_items_id_seq" for "serial" column "order_items.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "order_items_pkey" for table "order_items" ERROR: there is no unique constraint matching given keys for referenced table "orders" business-test-db=# i'm a RTFM man, but i miss the point from the documentation obviously, because what i don't understand is why the referenced column isn't considered to be unique. More doubts come into play when i see that the referenced key customers(customer_code) by the referencing table orders gives no errors. I'm not a native english speaker so probably that gives some more difficulties. Thanks in advance to all will contribute a focusing help. best regards from a proude-to-be postgresql user :-), Gianluca Riccardi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DB design and foreign keys
Table orders defines the column order_code as a serial, which simple makes a trigger which gives a new value to the column on insert. Note that there is NO guarantee that ths column will be unique. You can manually update the value to whatever you want. If you wish this column to be unique, you must specify it on creation, or later do an alter table add constraint to the column. A foreign key requires that the referenced column be unique (DB enforced, not just coincidentally unique), and that' s why your table creation is failing. Gianluca Riccardi wrote: hello all, i'm usign PostgreSQL 7.4.7 in a Debian 3.1 following is the SQL schema of my (very)small DB for a (very small)web business application: -- SQL schema for business-test-db CREATE TABLE customers ( customer_code serial, alfa_customer_code varchar(6), customer_name character varying(250) NOT NULL, address character varying(250) NOT NULL, city character varying(250) NOT NULL, zip_code character varying(8) NOT NULL, prov character varying(30) NOT NULL, security character varying(15) NOT NULL, tel character varying(30), tel2 character varying(20) NOT NULL, fax character varying(250), url character varying(250), email1 character varying(250) NOT NULL, email2 character varying(250) NOT NULL, discount1 integer, discount2 integer, PRIMARY KEY (customer_code) ); CREATE TABLE users ( id smallint NOT NULL, login varchar(20) NOT NULL, pwd varchar(20) NOT NULL, name varchar(20) NOT NULL, customer_code int REFERENCES customers (customer_code), valid date, primary key (id) ); CREATE TABLE products ( id serial, code varchar(60) UNIQUE NOT NULL, description varchar(250) NOT NULL, dimensions varchar(250) NOT NULL, price numeric NOT NULL, state boolean, PRIMARY KEY (id) ); CREATE TABLE orders ( id serial, order_code serial, customer_code integer REFERENCES customers (customer_code) NOT NULL, order_date time without time zone NOT NULL, remote_ip inet NOT NULL, order_time timestamp with time zone NOT NULL, order_type varchar(10) NOT NULL, state varchar(10) NOT NULL, PRIMARY KEY (id, order_code) ); CREATE TABLE order_items ( id serial, order_code integer REFERENCES orders (order_code) NOT NULL, customer_code integer REFERENCES customers (customer_code) NOT NULL, product_code varchar(60) REFERENCES products (code) NOT NULL, qty int NOT NULL, price numeric REFERENCES products (price) NOT NULL, row_price numeric, PRIMARY KEY (id, order_code) ); -- -- END OF FILE the tables: customers, users, products and orders are created as the SQL states. when i try to create the table order_items postgresql gives the following error: business-test-db=# CREATE TABLE order_items ( business-test-db(#id serial, business-test-db(#order_code integer REFERENCES orders (order_code) NOT NULL, business-test-db(#customer_code integer REFERENCES customers (customer_code) NOT NULL, business-test-db(#product_code varchar(60) REFERENCES products (code) NOT NULL, business-test-db(#qty int NOT NULL, business-test-db(#price numeric REFERENCES products (price) NOT NULL, business-test-db(#row_price numeric, business-test-db(#PRIMARY KEY (id, order_code) business-test-db(# ); NOTICE: CREATE TABLE will create implicit sequence "order_items_id_seq" for "serial" column "order_items.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "order_items_pkey" for table "order_items" ERROR: there is no unique constraint matching given keys for referenced table "orders" business-test-db=# i'm a RTFM man, but i miss the point from the documentation obviously, because what i don't understand is why the referenced column isn't considered to be unique. More doubts come into play when i see that the referenced key customers(customer_code) by the referencing table orders gives no errors. I'm not a native english speaker so probably that gives some more difficulties. Thanks in advance to all will contribute a focusing help. best regards from a proude-to-be postgresql user :-), Gianluca Riccardi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DB design and foreign keys
Gianluca Riccardi <[EMAIL PROTECTED]> writes: > CREATE TABLE orders ( >id serial, >order_code serial, >customer_code integer REFERENCES customers (customer_code) NOT NULL, >order_date time without time zone NOT NULL, >remote_ip inet NOT NULL, >order_time timestamp with time zone NOT NULL, >order_type varchar(10) NOT NULL, >state varchar(10) NOT NULL, >PRIMARY KEY (id, order_code) > ); > when i try to create the table order_items postgresql gives the > following error: > business-test-db=# CREATE TABLE order_items ( > business-test-db(#id serial, > business-test-db(#order_code integer REFERENCES orders (order_code) > NOT NULL, > business-test-db(#customer_code integer REFERENCES customers > (customer_code) NOT NULL, > business-test-db(#product_code varchar(60) REFERENCES products > (code) NOT NULL, > business-test-db(#qty int NOT NULL, > business-test-db(#price numeric REFERENCES products (price) NOT NULL, > business-test-db(#row_price numeric, > business-test-db(#PRIMARY KEY (id, order_code) > business-test-db(# ); > NOTICE: CREATE TABLE will create implicit sequence "order_items_id_seq" > for "serial" column "order_items.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "order_items_pkey" for table "order_items" > ERROR: there is no unique constraint matching given keys for referenced > table "orders" > i'm a RTFM man, but i miss the point from the documentation obviously, > because what i don't understand is why the referenced column isn't > considered to be unique. order_code is not by itself unique --- SERIAL doesn't guarantee that. I'm not sure why you are declaring the primary key of orders as being the combination of *two* serial columns, but if that's what you really need and you also want to be able to reference a row by just one of them, you'll need to apply a separate unique constraint to just the order_code column. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] DB design and foreign keys
[...unnecesary...] > CREATE TABLE orders ( > id serial, > order_code serial, > customer_code integer REFERENCES customers (customer_code) NOT NULL, > order_date time without time zone NOT NULL, > remote_ip inet NOT NULL, > order_time timestamp with time zone NOT NULL, > order_type varchar(10) NOT NULL, > state varchar(10) NOT NULL, > PRIMARY KEY (id, order_code) ^^^ > ); [...unnecesary...] > > CREATE TABLE order_items ( > id serial, > order_code integer REFERENCES orders (order_code) NOT NULL, ^^ [...unnecesary...] > ERROR: there is no unique constraint matching given keys for referenced > table "orders" this is because the PK in the orders table has two fields not one... so it founds no unique index on orders(order_code) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DB design and foreign keys
Gianluca Riccardi wrote: hello all, i'm usign PostgreSQL 7.4.7 in a Debian 3.1 CREATE TABLE orders ( id serial, order_code serial, ... PRIMARY KEY (id, order_code) ); CREATE TABLE order_items ( id serial, order_code integer REFERENCES orders (order_code) NOT NULL, when i try to create the table order_items postgresql gives the following error: ERROR: there is no unique constraint matching given keys for referenced table "orders" It means what it says. You have defined table orders with a primary key of (id,order_code). This means that the combination of (id,order_code) must be unique. So - these could all exist at the same time: (1,1), (1,2), (2,1), (2,2) You could not then add another (1,2) combination. Since id and order_code are both just automatically-generated numbers in the orders table it doesn't add anything to make both of them part of a primary-key. I would delete the id column altogether and just have the order_code as the primary-key (since "order_code" carries more meaning to a human than "id"). This means your order_items table can then safely reference the order_code it wants to. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] DB design and foreign keys
On Tue, 2005-12-13 at 12:16, Gianluca Riccardi wrote: > hello all, > i'm usign PostgreSQL 7.4.7 in a Debian 3.1 > > following is the SQL schema of my (very)small DB for a (very small)web > business application: > CREATE TABLE orders ( >id serial, >order_code serial, >customer_code integer REFERENCES customers (customer_code) NOT NULL, >order_date time without time zone NOT NULL, >remote_ip inet NOT NULL, >order_time timestamp with time zone NOT NULL, >order_type varchar(10) NOT NULL, >state varchar(10) NOT NULL, >PRIMARY KEY (id, order_code) > ); Given this table layout, I'm gonna take a wild guess and ask if you're coming from MySQL and expecting the second serial order_code to be a sub-autoincrement to id? If so, it won't be. That's a mysqlism. If you want something similar, you'll have to implement it yourself, and note that such a thing tends to be a poor performer with lots of parallel updates, and it can also be susceptible to race conditions if no locking is used. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] exporting Excel tables into PostgreSQL database with Python
Hi, On Tue, 13 Dec 2005, Jürgen Kemeter wrote: Hi! Here is a hopefully convenient description of my situation: - I have a main folder, containing several subfolders. - Every (sub)folder contains one or more .xls - Workbooks. - Every Workbook contains one or more different Spreadsheets. - The workbooks contain some cells which have Hyperlink addresses to other, relating workbooks. - Some cells in wokrbooks contain comments, which must also be exported. [snipp] I am not familiar with python but we have done imports and exports from and to excel in perl using the Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules from CPAN. Greetings Christian -- Christian Kratzer [EMAIL PROTECTED] CK Software GmbHhttp://www.cksoft.de/ Phone: +49 7452 889 135 Fax: +49 7452 889 136 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] # of 5 minute intervals in period of time ...
Is there a simpler way of doing this then:
select (date_part('epoch', now()) -
date_part('epoch', now() - '30 days'::interval)) / ( 5 * 60 );
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Multi-row update w. plpgsql function
Given a set of checkbox values that are submitted through an html form, how do you loop through the submitted values to update more than one row in a table? Imagine a table called 'message_table': mid | message | status +-+--- 1 | Text1 | H 2 | Text2 | H 3 | Text3 | H 4 | Text4 | H A web page presents the user with all messages flagged with 'H'. User checks messages 1,3 and 4 and submits form. (i.e. approved=1&approved=3&approved=4) After performing postgreSQL update, rows 1, 3 and 4 would be updated to: mid | message | status +-+--- 1 | Text1 | A 2 | Text2 | H 3 | Text3 | A 4 | Text4 | A I have never written a plpgsql function, but tried: CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS integer AS $body$ DECLARE new_status varchar; new_sample record; BEGIN new_status := 'A'; FOR new_sample IN SELECT * FROM message_table WHERE status='H' ORDER BY mid LOOP UPDATE message_table SET status = new_status WHERE mid = approved; END LOOP; RETURN 1; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; I call the function with: SELECT update_messages(); I'm using apache cocoon, which is why you see the variable placeholder: ); Unfortunately, the function only updates the first value submitted (mid 1), and doesn't loop through the other two values submitted. Can someone help this novice from getting ulcers? Thanks for your help! Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Multi-row update w. plpgsql function
Daniel Hertz wrote:
> Given a set of checkbox values that are submitted through an
> html form,
> how do you loop through the submitted values to update more
> than one row
> in a table?
>
> Imagine a table called 'message_table':
>
> mid | message | status
> +-+---
> 1 | Text1 | H
> 2 | Text2 | H
> 3 | Text3 | H
> 4 | Text4 | H
>
> A web page presents the user with all messages flagged with 'H'. User
> checks messages 1,3 and 4 and submits form.
> (i.e. approved=1&approved=3&approved=4)
>
> After performing postgreSQL update, rows 1, 3 and 4 would be
> updated to:
>
> mid | message | status
> +-+---
> 1 | Text1 | A
> 2 | Text2 | H
> 3 | Text3 | A
> 4 | Text4 | A
BEGIN;
UPDATE message_table SET status = 'A' WHERE mid = 1;
UPDATE message_table SET status = 'A' WHERE mid = 3;
UPDATE message_table SET status = 'A' WHERE mid = 4;
COMMIT;
would do that. Have your application generate an appropriate UPDATE line for
each "approved" entry in the form data, wrap it in a transaction, and away you
go.
> I have never written a plpgsql function, but tried:
>
> CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS
> integer AS
> $body$
> DECLARE
> new_status varchar;
> new_sample record;
>
> BEGIN
> new_status := 'A';
>
> FOR new_sample IN SELECT * FROM message_table WHERE
> status='H' ORDER BY
> mid LOOP
1. No need for ORDER BY here, we're not doing anything user-visible or
order-dependent with the data, so it's a waste of CPU time.
2. You're not using new_sample for anything, just retrieving it.
> UPDATE message_table SET status = new_status
> WHERE mid = approved;
Consider that you can only pass a single value to an INTEGER parameter
("approved"); this will repeatedly update a single entry where mid = approved.
You could simplify the function as written to
CREATE OR REPLACE FUNCTION update_messages (INTEGER) RETURNS VOID AS $$
UPDATE message_table SET status = 'A' WHERE mid = $1;
$$ LANGUAGE SQL;
> END LOOP;
>
> RETURN 1;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> I call the function with:
> SELECT update_messages();
>
> I'm using apache cocoon, which is why you see the variable
> placeholder:
> );
I'm not familiar with Cocoon, but I'd expect that to return only the first of
the "approved" values from the HTTP request. If you add logging to the stored
function (RAISE NOTICE 'approved: %', approved; near the start of the function,
for instance) and tell PostgreSQL to store the logs, you can see what values
your function is actually being called with.
What you really want to do is begin a transaction, loop over all the values of
approved present in the form data and call (the rewritten version of)
update_messages for each one, then commit the transaction.
-Owen
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Multi-row update w. plpgsql function
Owen makes a good point. Check that you are using the [] in the HTML input variable for the checkboxes. Like:
1
2
3
4
AaronOn 12/13/05, Owen Jacobson <[EMAIL PROTECTED]> wrote:
Daniel Hertz wrote:> Given a set of checkbox values that are submitted through an> html form,> how do you loop through the submitted values to update more> than one row> in a table?
>> Imagine a table called 'message_table':>> mid | message | status> +-+---> 1 | Text1 | H> 2 | Text2 | H> 3 | Text3 | H> 4 | Text4 | H
>> A web page presents the user with all messages flagged with 'H'. User> checks messages 1,3 and 4 and submits form.> (i.e. approved=1&approved=3&approved=4)>> After performing postgreSQL update, rows 1, 3 and 4 would be
> updated to:>> mid | message | status> +-+---> 1 | Text1 | A> 2 | Text2 | H> 3 | Text3 | A> 4 | Text4 | ABEGIN;
UPDATE message_table SET status = 'A' WHERE mid = 1;UPDATE message_table SET status = 'A' WHERE mid = 3;UPDATE message_table SET status = 'A' WHERE mid = 4;COMMIT;would
do that. Have your application generate an appropriate
UPDATE line for each "approved" entry in the form data, wrap it in a
transaction, and away you go.> I have never written a plpgsql function, but tried:>> CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS> integer AS> $body$> DECLARE
> new_status varchar;> new_sample record;>> BEGIN> new_status := 'A';>> FOR new_sample IN SELECT * FROM message_table WHERE> status='H' ORDER BY> mid LOOP
1. No
need for ORDER BY here, we're not doing anything user-visible or
order-dependent with the data, so it's a waste of CPU time.2. You're not using new_sample for anything, just retrieving it.> UPDATE message_table SET status = new_status> WHERE mid = approved;
Consider
that you can only pass a single value to an INTEGER parameter
("approved"); this will repeatedly update a single entry where mid =
approved. You could simplify the function as written toCREATE OR REPLACE FUNCTION update_messages (INTEGER) RETURNS VOID AS $$ UPDATE message_table SET status = 'A' WHERE mid = $1;$$ LANGUAGE SQL;
> END LOOP;>> RETURN 1;> END;> $body$> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;>> I call the function with:> SELECT update_messages();
>> I'm using apache cocoon, which is why you see the variable> placeholder:> );I'm
not familiar with Cocoon, but I'd expect that to return only the first
of the "approved" values from the HTTP request. If you add
logging to the stored function (RAISE NOTICE 'approved: %', approved;
near the start of the function, for instance) and tell PostgreSQL to
store the logs, you can see what values your function is actually being
called with.What you really want to do is begin a transaction,
loop over all the values of approved present in the form data and call
(the rewritten version of) update_messages for each one, then commit
the transaction.-Owen---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] lo function changed in PostgreSQL 8.1.1
Dear Tom, I'm not sure that I understand your word correctly. But I will explain more information I just need to keep my member's picture into database for showing and may have update sometime. I code my program with VB6. I found some suggestion to manage lo about lo_in and lo_out that why. What thing I need to do for continue using lo on PostgreSQL 8.1.1 Ragards, Premsun >>> Tom Lane <[EMAIL PROTECTED]> 12/13/2005 22:39:11 >>>"Premsun Choltanwanich" <[EMAIL PROTECTED]> writes:> lo (Large Object) function that normally shown in function list now => disappear. I'm sure that I already check on Large Object box when I => install. I found some information on http://www.postgresql.org/docs/8.1/int=> eractive/lo-interfaces.html#AEN26978 about lo function. PostgreSQL has => many new lo function that I never use in version 8.0.4 and some function I => have use is not found (lo_in , lo_out).lo_in/lo_out aren't needed anymore because lo isn't a separate type,just a domain over OID. Why were you using them directly, anyway?regards, tom lane
Re: [SQL] exporting Excel tables into PostgreSQL database with Python
O Christian Kratzer έγραψε στις Dec 13, 2005 : > Hi, > > On Tue, 13 Dec 2005, Jόrgen Kemeter wrote: > > Hi! > > > > Here is a hopefully convenient description of my situation: > > - I have a main folder, containing several subfolders. > > - Every (sub)folder contains one or more .xls - Workbooks. > > - Every Workbook contains one or more different Spreadsheets. > > - The workbooks contain some cells which have Hyperlink addresses to > > other, > > relating workbooks. > > - Some cells in wokrbooks contain comments, which must also be exported. > [snipp] > > I am not familiar with python but we have done imports and exports from > and to excel in perl using the Spreadsheet::ParseExcel and > Spreadsheet::WriteExcel modules from CPAN. Same here but with java, using jakarta-poi. > > Greetings > Christian > > -- -Achilleus ---(end of broadcast)--- TIP 6: explain analyze is your friend
