Transfer data from webpage form to database

Discussion in 'Python' started by Pete....., Nov 23, 2004.

  1. Pete.....

    Pete..... Guest

    Hi all.
    I have made a webpage where there is a webform where people can fill in
    their personel information:

    The code is below:


    I want to transfer the data to a postgreSQL database ( I have allready made
    the database with the neccesary tables, and I know how to connect to it )
    but I really have no idea how I can transfer the data from the webform to
    the database.

    If any can help me I will be more than pleased... thanks for your help...

    <html>

    <head>

    <title> Adminperson </title>

    </head>

    <body bgcolor="#0479ff">

    <h1><p align="center">Insert Person</h1></p>

    <hr>

    <form action="default.asp" method="post">
    <p>Name:<br><input type="text" name="name"></p>
    <p>Surname:<br><input type="text" name="surnavn"></p>
    <p>Username:<br><input type="text" name="usernavn"></p>
    <p>Password:<br><input type="text" name="password"></p>
    <p>Adresse:<br><input type="text" name="adresse"></p>
    <p>Zipcode:<br><input type="text" name="zipcode"></p>
    <p>City:<br><input type="text" name="city"></p>
    <p>Phone:<br><input type="text" name="phone"></p
    <p>Mail:<br><input type="text" name="mail"></p>


    <p> What is your profession? <p>
    <INPUT TYPE=CHECKBOX NAME="Profession" VALUE="Student" CHECKED> Student
    <INPUT TYPE=CHECKBOX NAME="Profession" VALUE="Teacher" CHECKED> Teacher
    <INPUT TYPE=CHECKBOX NAME="Profession" VALUE="Teacher Assistent" CHECKED>
    Teacher Assistent
    <p><input type="submit" value="Submit"></p>
    </body> </html>
     
    Pete....., Nov 23, 2004
    #1
    1. Advertising

  2. "Pete....." <> writes:

    > Hi all.
    > I have made a webpage where there is a webform where people can fill in
    > their personel information:
    >
    > The code is below:
    >
    >
    > I want to transfer the data to a postgreSQL database ( I have allready made
    > the database with the neccesary tables, and I know how to connect to it )
    > but I really have no idea how I can transfer the data from the webform to
    > the database.


    Your sample form shows being posted to an ASP file which you are going
    to change to a Python.cgi script, right?

    #!/usr/local/bin/python

    print 'content-type: text/plain\n'

    import cgi
    import someDatabaseAPI

    form = cgi.FieldStorage()

    someDatabaseAPI.connect()

    someDatabaseAPI.query("""
    insert into foo
    values ('%s', '%s')
    """ % (form[key1].value, form[key2].value))

    # where key1, key2... are the field names in HTML form

    Have a look at the cgi Python module where you will learn all about
    this.

    HTH


    --
    -------------------------------------------------------------------------------
    Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
    305 321-1144 (mobile http://www.JerrySievers.com/
     
    Jerry Sievers, Nov 23, 2004
    #2
    1. Advertising

  3. Pete.....

    Pete..... Guest

    Hi, yeah It's try I want to change it to a python.cgi.script. And thanks for
    your input.....

    I use the api pgsql
    So the first thing I do is to import the following and connect to the
    server:

    #!/pack/python-2.3.2/bin/python2.3
    from pyPgSQL import PgSQL
    import cgi
    import cgitb
    cgitb.enable()

    form = cgi.FieldStorage() ( I added this )
    connect = PgSQL.connect(user="vvvvv", password="zzzzz", host="xxx.xxx.xx",
    database="yyyyy")

    Do I then have to write:

    PgSQL.query(''' INSERT into form
    Select persons.idpersons, person.username,
    persons.surname, phone.phone (And so on ) ( its what the specific tables
    are called in my database)
    From persons, phone (
    And so on )
    Where persons.name = 'Name'%s
    AND persons.surname ='Surname'%s
    AND persons.username = 'Username'%s '''
    (And so on with the rest)
    % (form['name'].value, form['surname'].value,
    form['username'].value))

    I dont think I get it quite right, any help would be highly appreciated.
    Thanks for all help...


    Below is the code from my webpage form:

    <form action="default.asp" method="post">
    <p>Name:<br><input type="text" name="name"></p>
    <p>Surname:<br><input type="text" name="surnavn"></p>
    <p>Username:<br><input type="text" name="username"></p>
    <p>Password:<br><input type="text" name="password"></p>
    <p>Adresse:<br><input type="text" name="adresse"></p>
    <p>Zipcode:<br><input type="text" name="zipcode"></p>
    <p>City:<br><input type="text" name="city"></p>
    <p>Phone:<br><input type="text" name="phone"></p
    <p>Mail:<br><input type="text" name="mail"></p>


    <p> What is your profession? <p>
    <INPUT TYPE=CHECKBOX NAME="Profession" VALUE="Student" CHECKED> Student
    <INPUT TYPE=CHECKBOX NAME="Profession" VALUE="Teacher" CHECKED> Teacher
    <INPUT TYPE=CHECKBOX NAME="Profession" VALUE="Teacher Assistent"
    CHECKED> Teacher Assistent
    <p><input type="submit" value="Submit"></p>
    </body> </html>'''
     
    Pete....., Nov 23, 2004
    #3
  4. "Pete....." <> writes:

    > Hi, yeah It's try I want to change it to a python.cgi.script. And thanks for
    > your input.....
    >
    > I use the api pgsql
    > So the first thing I do is to import the following and connect to the
    > server:
    >
    > #!/pack/python-2.3.2/bin/python2.3
    > from pyPgSQL import PgSQL
    > import cgi
    > import cgitb
    > cgitb.enable()
    >
    > form = cgi.FieldStorage() ( I added this )
    > connect = PgSQL.connect(user="vvvvv", password="zzzzz", host="xxx.xxx.xx",
    > database="yyyyy")
    >
    > Do I then have to write:
    >
    > PgSQL.query(''' INSERT into form
    > Select persons.idpersons, person.username,
    > persons.surname, phone.phone (And so on ) ( its what the specific tables
    > are called in my database)
    > From persons, phone (
    > And so on )
    > Where persons.name = 'Name'%s
    > AND persons.surname ='Surname'%s
    > AND persons.username = 'Username'%s '''
    > (And so on with the rest)
    > % (form['name'].value, form['surname'].value,
    > form['username'].value))
    >
    > I dont think I get it quite right, any help would be highly appreciated.
    > Thanks for all help...


    It looks to me like the part here that you don't understand is how to
    interpolate your form data into the string for the database query.

    You should study up on the % string formatting operator in the Python
    docs.

    I'll throw you a bone here;

    """
    %s %s %s
    """ % (form[key1].value, form[key2].value, form[keyN].value)

    Each of those %s tokens is going to be replaced with one of the values
    in the tuple right of the % operator.

    If you want to pile the form values into a dict and then use symbolic
    names in the string...

    dict = {}
    for key in form:
    dict[key] = form[key].value

    """
    %(key1)s %(key2)s...
    """ % dict

    Maybe there is a function in the cgi module that will make the dict
    for you? I am not sure.

    Have fun!


    --
    -------------------------------------------------------------------------------
    Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
    305 321-1144 (mobile http://www.JerrySievers.com/
     
    Jerry Sievers, Nov 23, 2004
    #4
  5. Pete.....

    Pete..... Guest

    Hi again and thanks.
    I think I understand the meaning of string formatting operators, I also
    understand the meaning with % (form['name'].value, form['surname'].value,
    form['username'].value)) And that it is a dictionary that I use.

    The thing I find really hard to understand is how I can send the data from
    the webform to my postgresql database. I cant find the right commands, i
    been searching the web for days now without luck.

    PgSQL.query(''' INSERT into form Select persons.idpersons, person.username,
    persons.surname, phone.phone (And so on ) ( its what the specific tables is
    called in my db)
    From persons, phone( And so on )
    Where persons.name = 'Name'%s
    AND persons.surname ='Surname'%s
    AND persons.username = 'Username'%s ''' (And so on with the rest)

    I dont really know if I have to write Pgsql.query("INSERT into form
    .........................
    or if I have to write something else.
    I know that I have to take the input from my webform, and store each input
    ( with each input I mean what the user types in each field, could be the
    name, the username etc) in a "variable" so that I can put it into the right
    place in the db.
    I can see that I do this by using % (form['name'].value,
    form['surname'].value, form['username'].value))
    But I dont know how I put the data form['name].value in to my database in
    the table persons.name.

    Sorry for all this questions, just getting really confused with this little
    project of mine, thinks its fun, but I just cant figure it out anymore, and
    I cant find any info I can use on the web....

    Thanks for taking your time to read this....


    "Jerry Sievers" <> wrote in message
    news:...
    > "Pete....." <> writes:
    >
    >> Hi, yeah It's try I want to change it to a python.cgi.script. And thanks
    >> for
    >> your input.....
    >>
    >> I use the api pgsql
    >> So the first thing I do is to import the following and connect to the
    >> server:
    >>
    >> #!/pack/python-2.3.2/bin/python2.3
    >> from pyPgSQL import PgSQL
    >> import cgi
    >> import cgitb
    >> cgitb.enable()
    >>
    >> form = cgi.FieldStorage() ( I added this )
    >> connect = PgSQL.connect(user="vvvvv", password="zzzzz",
    >> host="xxx.xxx.xx",
    >> database="yyyyy")
    >>
    >> Do I then have to write:
    >>
    >> PgSQL.query(''' INSERT into form
    >> Select persons.idpersons, person.username,
    >> persons.surname, phone.phone (And so on ) ( its what the specific tables
    >> are called in my database)
    >> From persons, phone
    >> (
    >> And so on )
    >> Where persons.name = 'Name'%s
    >> AND persons.surname ='Surname'%s
    >> AND persons.username = 'Username'%s '''
    >> (And so on with the rest)
    >> % (form['name'].value, form['surname'].value,
    >> form['username'].value))
    >>
    >> I dont think I get it quite right, any help would be highly appreciated.
    >> Thanks for all help...

    >
    > It looks to me like the part here that you don't understand is how to
    > interpolate your form data into the string for the database query.
    >
    > You should study up on the % string formatting operator in the Python
    > docs.
    >
    > I'll throw you a bone here;
    >
    > """
    > %s %s %s
    > """ % (form[key1].value, form[key2].value, form[keyN].value)
    >
    > Each of those %s tokens is going to be replaced with one of the values
    > in the tuple right of the % operator.
    >
    > If you want to pile the form values into a dict and then use symbolic
    > names in the string...
    >
    > dict = {}
    > for key in form:
    > dict[key] = form[key].value
    >
    > """
    > %(key1)s %(key2)s...
    > """ % dict
    >
    > Maybe there is a function in the cgi module that will make the dict
    > for you? I am not sure.
    >
    > Have fun!
    >
    >
    > --
    > -------------------------------------------------------------------------------
    > Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
    > 305 321-1144 (mobile http://www.JerrySievers.com/
     
    Pete....., Nov 23, 2004
    #5
  6. Pete.....

    Alan Kennedy Guest

    [Pete]
    > [snip]
    > Below is the code from my webpage form:
    >
    > <form action="default.asp" method="post">


    You should note that the action attribute of a form must contain the URL
    to which the form variables will be POSTed.

    If you are writing a python CGI script, then action="default.asp" is
    almost certainly wrong, unless you have called your python script
    "default.asp".

    The URL for your python script is the value that should go in the action
    attribute, like so

    <form action="http://mydomain.com/cgi-bin/my_cgi_script.py" method="post">

    or you could leave out the server name bit.

    <form action="/cgi-bin/my_cgi_script.py" method="post">

    HTH,

    --
    alan kennedy
    ------------------------------------------------------
    email alan: http://xhaus.com/contact/alan
     
    Alan Kennedy, Nov 23, 2004
    #6
  7. "Pete....." <> writes:

    > Hi again and thanks.
    > I think I understand the meaning of string formatting operators, I also
    > understand the meaning with % (form['name'].value, form['surname'].value,
    > form['username'].value)) And that it is a dictionary that I use.
    >
    > The thing I find really hard to understand is how I can send the data from
    > the webform to my postgresql database. I cant find the right commands, i
    > been searching the web for days now without luck.
    >
    > PgSQL.query(''' INSERT into form Select persons.idpersons, person.username,
    >


    You mean that whatever docs for the PgAPI that you are using don't
    explain?

    I am using pygresql. There are others.

    from pgdb import connect

    conn = connect(database = 'db', username = 'user', password = 'passwd')
    cursor = conn.cursor()

    cursor.execute("insert into foo values (1)")
    conn.commit()

    That's about all the help I can give you.

    Good luck

    --
    -------------------------------------------------------------------------------
    Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
    305 321-1144 (mobile http://www.JerrySievers.com/
     
    Jerry Sievers, Nov 23, 2004
    #7
  8. Pete.....

    Andrew James Guest

    Pete,
    What you're looking for is an INSERT SQL query. You can find the basic
    theory and some more advanced bits 'n pieces here (specifically
    PostgreSQL):

    http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x5504.htm

    The question that you're asking is so vague that you should think about
    reading up on SQL before going any further - you may find yourself
    quickly overwhelmed otherwise.

    Andrew

    On Tue, 2004-11-23 at 20:43 +0000, Alan Kennedy wrote:
    > [Pete]
    > > [snip]
    > > Below is the code from my webpage form:
    > >
    > > <form action="default.asp" method="post">

    >
    > You should note that the action attribute of a form must contain the URL
    > to which the form variables will be POSTed.
    >
    > If you are writing a python CGI script, then action="default.asp" is
    > almost certainly wrong, unless you have called your python script
    > "default.asp".
    >
    > The URL for your python script is the value that should go in the action
    > attribute, like so
    >
    > <form action="http://mydomain.com/cgi-bin/my_cgi_script.py" method="post">
    >
    > or you could leave out the server name bit.
    >
    > <form action="/cgi-bin/my_cgi_script.py" method="post">
    >
    > HTH,
    >
    > --
    > alan kennedy
    > ------------------------------------------------------
    > email alan: http://xhaus.com/contact/alan

    --
    Andrew James <>
     
    Andrew James, Nov 23, 2004
    #8
  9. Pete.....

    Pete..... Guest

    First thanks for all your friendly input and for looking at this once
    again...

    I think I made some progress, but its still not working, so I must still do
    something wrong.
    Here is how far I have come.

    I made an insertintotable script, that I want to run every time a person
    presses "The submit button on my webpage" It goes like this:
    --------------------------------------------------------------------
    #!/pack/python-2.3.2/bin/python2.3

    from pyPgSQL import PgSQL
    import cgi
    import cgitb
    cgitb.enable()

    form = cgi.FieldStorage()

    connect = PgSQL.connect(user="user", password="password", host="host",
    database="database")
    cur = connect.cursor()
    cur.execute('''INSERT INTO persons (name, surname, username, password)
    (VALUES(%s,%s, %s, %s)''')
    %(form[name].value, form[surname].value, form[username].value,
    form[password].value)
    ---------------------------------------------------------------------------------------------
    My question is, do I need more code to tell the program that I want to
    transfer the user input ( on my webpage ) to my postgresql database.
    ---------------------------------------------------------------------------
    The code from my webpage is:

    #!/pack/python-2.3.2/bin/python2.3

    from pyPgSQL import PgSQL
    import cgi
    import cgitb
    cgitb.enable()

    print '''

    <html>

    <head>

    <title> Adminperson </title>

    </head>

    <body bgcolor="#0479ff">

    <h1><p align="center">Insert Person</h1></p>

    <hr>

    <form action="insertintotable.py" method="get">
    <p>Name:<br><input type="text" name="name"></p>
    <p>Surname:<br><input type="text" name="surnavn"></p>
    <p>Username:<br><input type="text" name="usernavn"></p>
    <p>Password:<br><input type="text" name="password"></p>
    <p><input type="submit" value="Submit"></p>
    </body> </html>'''
    ------------------------------------------------------------------------
    And once again thanks...
     
    Pete....., Nov 24, 2004
    #9
  10. Pete.....

    Pete..... Guest

    The error I get is that when this code starts: %(form[name].value,
    form[surname].value, form[username].value, form[password].value)
    it says that NameError: name 'name' is not defined
    args = ("name 'nama' is not defined",)
    which is from the html webpage form code
    form action="insertintotable.py" method="get">
    <p>Name:<br><input type="text" name="name"></p>

    Any ideas ?
    Thanks all

    "Pete....." <> wrote in message
    news:41a3ccf2$0$29450$...
    > First thanks for all your friendly input and for looking at this once
    > again...
    >
    > I think I made some progress, but its still not working, so I must still
    > do something wrong.
    > Here is how far I have come.
    >
    > I made an insertintotable script, that I want to run every time a person
    > presses "The submit button on my webpage" It goes like this:
    > --------------------------------------------------------------------
    > #!/pack/python-2.3.2/bin/python2.3
    >
    > from pyPgSQL import PgSQL
    > import cgi
    > import cgitb
    > cgitb.enable()
    >
    > form = cgi.FieldStorage()
    >
    > connect = PgSQL.connect(user="user", password="password", host="host",
    > database="database")
    > cur = connect.cursor()
    > cur.execute('''INSERT INTO persons (name, surname, username, password)
    > (VALUES(%s,%s, %s, %s)''')
    > %(form[name].value, form[surname].value, form[username].value,
    > form[password].value)
    > ---------------------------------------------------------------------------------------------
    > My question is, do I need more code to tell the program that I want to
    > transfer the user input ( on my webpage ) to my postgresql database.
    > ---------------------------------------------------------------------------
    > The code from my webpage is:
    >
    > #!/pack/python-2.3.2/bin/python2.3
    >
    > from pyPgSQL import PgSQL
    > import cgi
    > import cgitb
    > cgitb.enable()
    >
    > print '''
    >
    > <html>
    >
    > <head>
    >
    > <title> Adminperson </title>
    >
    > </head>
    >
    > <body bgcolor="#0479ff">
    >
    > <h1><p align="center">Insert Person</h1></p>
    >
    > <hr>
    >
    > <form action="insertintotable.py" method="get">
    > <p>Name:<br><input type="text" name="name"></p>
    > <p>Surname:<br><input type="text" name="surnavn"></p>
    > <p>Username:<br><input type="text" name="usernavn"></p>
    > <p>Password:<br><input type="text" name="password"></p>
    > <p><input type="submit" value="Submit"></p>
    > </body> </html>'''
    > ------------------------------------------------------------------------
    > And once again thanks...
    >
     
    Pete....., Nov 24, 2004
    #10
  11. Pete.....

    Pete..... Guest

    Hi I got that error debugged but, now there is a new one:
    12 cur.execute('''INSERT INTO persons (persons.name,
    persons.surname, persons.username, persons.password) VALUES %s,%s, %s, %s
    ''' %(form['name'].value, form['surname'].value, form['username'].value,
    form['password'].value))

    13

    cur = <pyPgSQL.PgSQL.Cursor instance>, cur.execute = <bound method
    Cursor.execute of <pyPgSQL.PgSQL.Cursor instance>>, form =
    FieldStorage(None, None, [MiniFieldStorage('name...name', 'sa'),
    MiniFieldStorage('password', 'a')]), ].value = [MiniFieldStorage('name',
    's'), MiniFieldStorage('surname', 'd'), MiniFieldStorage('username', 'sa'),
    MiniFieldStorage('password', 'a')]

    /pack/python-2.3.2/lib/python2.3/site-packages/pyPgSQL/PgSQL.py in
    execute(self=<pyPgSQL.PgSQL.Cursor instance>, query='INSERT INTO persons
    (persons.name, persons.surna...ns.username, persons.password) VALUES s,d,
    sa, a ', *parms=())
    3070 self.conn.__dict__["inTransaction"] = 0

    3071 self.conn._Connection__closeCursors()

    3072 raise OperationalError, msg

    3073 except InternalError, msg:

    3074 # An internal error occured. Try to get to a sane
    state.

    global OperationalError = <class libpq.OperationalError>, msg =
    <libpq.OperationalError instance>


    OperationalError: ERROR: syntax error at or near "." at character 29
    args = ('ERROR: syntax error at or near "." at character 29\n',)

    Cant figure that one out....

    Thanks for all help.....
     
    Pete....., Nov 24, 2004
    #11
  12. Pete.....

    Mark Roach Guest

    On Wed, 2004-11-24 at 02:16 +0100, Pete..... wrote:
    > Hi I got that error debugged but, now there is a new one:
    > 12 cur.execute('''INSERT INTO persons (persons.name,
    > persons.surname, persons.username, persons.password) VALUES %s,%s, %s, %s
    > ''' %(form['name'].value, form['surname'].value, form['username'].value,
    > form['password'].value))


    That syntax doesn't look right. I think you were closer with your
    previous attempt. This is slightly more correct

    cur.execute('''INSERT INTO persons (name, surname, username, password)
    VALUES('%s','%s', '%s', '%s')''' % (form[name].value,
    form[surname].value, form[username].value, form[password].value))

    (Note the single quotes around the %s)

    The real problem with this code is that you are letting the user of your
    website inject whatever SQL they want directly into your command. I am
    not sure if this works for pypgsql, but with psycopg the safe way to do
    this is

    insert_command = '''
    INSERT INTO persons (name, surname, username, password)
    VALUES(%s, %s, %s, %s)
    '''
    cur.execute(insert_command,
    (form[name].value, form[surname].value, \
    form[username].value, form[password].value))

    I believe this works with other DB API 2.0 compatible modules. This lets
    the database module worry about whether "jim's house" needs to be turned
    into "jim\\'s house" or "'jim\\'s house'"

    HTH

    -Mark
     
    Mark Roach, Nov 24, 2004
    #12
  13. Mark Roach <> writes:

    > On Wed, 2004-11-24 at 02:16 +0100, Pete..... wrote:
    >
    > That syntax doesn't look right. I think you were closer with your
    > previous attempt. This is slightly more correct
    >
    > cur.execute('''INSERT INTO persons (name, surname, username, password)
    > VALUES('%s','%s', '%s', '%s')''' % (form[name].value,
    > form[surname].value, form[username].value, form[password].value))
    >
    > (Note the single quotes around the %s)
    >
    > The real problem with this code is that you are letting the user of your
    > website inject whatever SQL they want directly into your command. I am
    > not sure if this works for pypgsql, but with psycopg the safe way to do
    > this is


    If you are using pygresql the execute() method allows an optional dict
    as arg2 and if provided, will be quoted according to data types and
    the LHS string integrated with the dict as if by the % operator.

    cur.execute('query')
    cur.execute('query with hand quoted args %s' % (foo))
    cur.execute('query with args quoted by API %(name1)s, %(name2)d',dict)

    String types will get wrapped in single quotes and have embedded
    single quotes escaped, the most common use but other types such as
    datetime will get special treatment as well.

    I agree with you. Any up to date API should have this capability
    which can always be overridded in an extreme case.

    Bye

    --
    -------------------------------------------------------------------------------
    Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
    305 321-1144 (mobile http://www.JerrySievers.com/
     
    Jerry Sievers, Nov 24, 2004
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    432
  2. Paul
    Replies:
    14
    Views:
    854
    Alexey Smirnov
    Jun 19, 2008
  3. sifar
    Replies:
    5
    Views:
    429
  4. Replies:
    5
    Views:
    116
    McKirahan
    Sep 6, 2006
  5. soren625
    Replies:
    2
    Views:
    379
    soren625
    Dec 12, 2006
Loading...

Share This Page