Re: I'm very confused. - Mailing list pgsql-novice

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brant Fitzsimmons wrote:
> Hi there,
>
> I think that this may be my first post to this list so be gentle.
>
> I'm having a very strange problem (to me) and I hope that someone
> can shed some light on what I'm not seeing.
>
> I created a database and table from the command line using: sudo su
>  postgres createdb test
>
> That worked fine and created the stated table.
>
> I then logged in using: sudo su postgres psql test
>
> Again, that worked fine.
>
> I created a table from the prompt using: create table users (userID
>  serial, userName varchar(32), firstName varchar(32), lastName
> varchar(64), email varchar(128));
>
> The table was successfully created.  So good so far.  Don't mind
> the values of the varchar's; this is purely for testing.
>
> I then added a primary key to the table using:  alter table users
> add primary key (userId);
>
> Again, it worked.
>
> Here's where it starts to get weird.
>
> I inserted data into the table using: insert into users (username,
> firstName, lastName, email) values ('bfitzsimmons', 'Brant',
> 'Fitzsimmons', 'brant@bfcomputerconsulting.com');
>
> It went in properly and I was able to select the inserted data
> without any problems (select * from users;).
>
> I then wrote a python script to insert and select data from the
> table.  The script is able to insert and retrieve data without any
> problems...*but*...from the console I can't retrieve any of the
> records inserted by the script.
>
> Records that are entered at the console show up when I do a select
> with the script, but not the other way around.  I have tried to the
>  RTFM as much as possible, but I'm still lost as to why while
> accessing the same table I can't access the data manipulated by the
> script.  I don't know if it has anything to do with tablespaces or
> what, but I'm really starting to get frustrated.
>
> I'm from a MySQL background and I'm completely stumped as to how
> you can, through a script, insert data into a database *created at
> the console* and not be able to select that data from the console.
> I know it's there taking up space on my machine, and I can select
> it from the script, but I can't manipulate it in any way outside of
> that.
>
> Any thoughts?

Something I just noticed is that when I run the script:

#!/usr/bin/python

# import pgdb module
import pgdb

# connect to the db
db = pgdb.connect(host="localhost", database="test", user="postgres",
password="*********")

# instantiate cursor
cursor = db.cursor()

# insert 10 records
for x in range(10):
    cursor.execute("INSERT INTO users (username, firstname, lastname,
email, age) VALUES ('bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31)")

# retrieve the data
cursor.execute("SELECT * FROM users")
result = cursor.fetchall()

# print data to the screen
for line in result:
    print line




I end up seeing only ten records printed to the screen, when I should
see 10, 20, 30, etc. every time I run the script.  The serial column
(userId) is incrementing but I'm not seeing any more that 10 results
from the select query.


- From the console:
test=# select * from users;
 userid | username | firstname | lastname | email | age
- --------+----------+-----------+----------+-------+-----
(0 rows)


Output from the script above:
bfitzsimmons@epsilon:~/test$ ./postgresql_test.py
[200313, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200314, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200315, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200316, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200317, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200318, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200319, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200320, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200321, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
[200322, 'bfitzsimmons', 'Brant', 'Fitzsimmons',
'brant@bfcomputerconsulting.com', 31]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFrdbRDpzwx2t8E5gRAhxAAJoC2NQ1JbAfV+DYavVUwUPpTo5+vgCglIcI
UQNMBNEXH46qktyNwqt5AFY=
=Bsok
-----END PGP SIGNATURE-----