Dictionary inserts into MySQL (each key in its own field)

Discussion in 'Python' started by Derick van Niekerk, Jan 27, 2006.

  1. I have found many posts that deal with writing a dictionary to MySQL in
    a blob field - which I can't imagine why anybody would want to do it.

    I want to write each element of a dictionary onto a db table. The keys
    would match the fieldnames. Is there something that would make this job
    easier? i.e. how do I write an entire dictionary into a db table as a
    row? I have an idea that it is something very simple - possibly even
    one line of code...

    -d-
     
    Derick van Niekerk, Jan 27, 2006
    #1
    1. Advertising

  2. On Fri, 27 Jan 2006 00:03:30 -0800, Derick van Niekerk wrote:

    > I have found many posts that deal with writing a dictionary to MySQL in
    > a blob field - which I can't imagine why anybody would want to do it.
    >
    > I want to write each element of a dictionary onto a db table. The keys
    > would match the fieldnames. Is there something that would make this job
    > easier? i.e. how do I write an entire dictionary into a db table as a
    > row? I have an idea that it is something very simple - possibly even
    > one line of code...
    >
    > -d-



    Try looking at http://pear.php.net/db , in the documentation, see
    DB::autoPrepare and DB::autoExecute.

    -Rob
     
    Robin Haswell, Jan 27, 2006
    #2
    1. Advertising

  3. On Fri, 27 Jan 2006 00:03:30 -0800, Derick van Niekerk wrote:

    > I have found many posts that deal with writing a dictionary to MySQL in
    > a blob field - which I can't imagine why anybody would want to do it.
    >
    > I want to write each element of a dictionary onto a db table. The keys
    > would match the fieldnames. Is there something that would make this job
    > easier? i.e. how do I write an entire dictionary into a db table as a
    > row? I have an idea that it is something very simple - possibly even
    > one line of code...
    >
    > -d-


    Oh crap sorry, ignore my last message - I thought I was still looking at
    c.l.php :)

    I can't answer your question in Python, sorry :)

    -Rob
     
    Robin Haswell, Jan 27, 2006
    #3
  4. Derick van Niekerk wrote:

    > I have found many posts that deal with writing a dictionary to MySQL in
    > a blob field - which I can't imagine why anybody would want to do it.


    it might be useful if you have a bunch of unknown properties (e.g. configuration
    parameters for some external parameters), and no need to access the properties
    as individual parameters via the database.

    > I want to write each element of a dictionary onto a db table. The keys
    > would match the fieldnames. Is there something that would make this job
    > easier? i.e. how do I write an entire dictionary into a db table as a
    > row? I have an idea that it is something very simple - possibly even
    > one line of code...


    some database drivers have API:s for this, but if you're using a generic
    DB-API driver, it's pretty straightforward to generate an appropriate SQL
    statement on the fly; e.g.

    d = {"spam": "1", "egg": "2"}

    cols = d.keys()
    vals = d.values()

    stmt = "INSERT INTO table (%s) VALUES(%s)" % (
    ",".join(cols), ",".join(["?"]*len(vals))
    )

    cursor.execute(stmt, tuple(vals))

    </F>
     
    Fredrik Lundh, Jan 27, 2006
    #4
  5. I will be using the python-mysql API. This looks like what I am looking
    for. I just have a question about the last join statment. In this case
    it would just create a string = '?,?' - wouldn't it? Other than that,
    it is pretty easy to understand. Now - how do I escape the string for
    characters that might break the script e.g. [' " \ ) ( ...]?

    Is there a python function to do it? part of the mysql-python module,
    maybe?

    -d-
     
    Derick van Niekerk, Jan 27, 2006
    #5
  6. Derick van Niekerk wrote:
    >
    >
    > I will be using the python-mysql API. This looks like what I am looking
    > for. I just have a question about the last join statment. In this case
    > it would just create a string = '?,?' - wouldn't it? Other than that,
    > it is pretty easy to understand. Now - how do I escape the string for
    > characters that might break the script e.g. [' " \ ) ( ...]? [...]


    You don't need to escape the strings, because the example code uses the
    parametrized form of the cursor.execute statement, and the DB-API module
    will just do the right thing.

    The example code will maybe not work like this, because IIRC MySQLdb
    uses paramstyle = "format", and not paramstyle = "qmark". I. e. you will
    have to use %s as placeholders in the query, and not ?. So you would
    have to replace the ",".join(["?"]*len(vals)) with
    ",".join(["%s"]*len(vals)).

    -- Gerhard
     
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Jan 27, 2006
    #6
  7. Derick van Niekerk wrote:

    >
    >
    > I will be using the python-mysql API. This looks like what I am looking
    > for. I just have a question about the last join statment. In this case
    > it would just create a string = '?,?' - wouldn't it?


    yup. those are parameter markers (replace with "%s" if that's what
    your database API is using). each ? (or %s) corresponds to a value
    in the tuple argument to execute. e.g.

    execute("INSERT INTO table (foo, bar) VALUES(?, ?)", ('one', 'two'))

    is the same thing as

    execute("INSERT INTO table (foo, bar) VALUES('one', 'two')")

    except that the former is 1) more efficient, and 2) safe, since the
    parameter values are passed directly to the SQL engine, without
    going through the SQL parser. there's no need to escape them.

    > Now - how do I escape the string for characters that might break
    > the script e.g. [' " \ ) ( ...]?


    you don't -- that's why the values are passed in as a separate argument
    to execute.

    (just curious, but from where do people get the idea that arbitrary data
    just have to be inserted into the the SQL statement text all the time? is
    this some PHP misfeature?)

    </F>
     
    Fredrik Lundh, Jan 27, 2006
    #7
  8. I've never seen it done in this way before, but I do come from a PHP
    point of view.

    I've only started with python this week, so a lot of the way it does
    things are new to me, So far, all of the differences are good.

    Thanks for the help
     
    Derick van Niekerk, Jan 27, 2006
    #8
  9. On Fri, 2006-01-27 at 05:47, Fredrik Lundh wrote:
    > (just curious, but from where do people get the idea that arbitrary data
    > just have to be inserted into the the SQL statement text all the time? is
    > this some PHP misfeature?)


    Yes, the need to roll queries by inserting parameters directly into the
    query string is definitely a PHP misfeature (for versions less than 5),
    since the database access modules don't accommodate parametrized
    queries. PHP5 finally introduced a standardized database API that allows
    parametrized queries. Of course, Python has had this since 1996.

    -Carsten
     
    Carsten Haese, Jan 27, 2006
    #9
    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. Fernie
    Replies:
    9
    Views:
    1,028
    David Dorward
    Dec 5, 2004
  2. Saverio M.
    Replies:
    0
    Views:
    558
    Saverio M.
    Jul 3, 2006
  3. Martin P. Hellwig

    Dictionary inserts and threads

    Martin P. Hellwig, Jan 27, 2007, in forum: Python
    Replies:
    1
    Views:
    271
    Paul Rubin
    Jan 27, 2007
  4. thunk
    Replies:
    1
    Views:
    366
    thunk
    Mar 30, 2010
  5. thunk
    Replies:
    0
    Views:
    541
    thunk
    Apr 1, 2010
Loading...

Share This Page