Mysql to Sqlite Update SET not working

Discussion in 'Ruby' started by Grant Gray, Jul 11, 2009.

  1. Grant Gray

    Grant Gray Guest

    Hi,

    I have been trying to get UPDATE SET working to move data from MYSQL to
    SQLite3.

    The code is as follows:

    #!/usr/bin/env ruby

    require 'dbi'
    require 'rubygems'
    require 'sqlite3'
    require 'mysql'

    my1 = Mysql.real_connect("server", "username", "password", "db")
    my2 = SQLite3::Database.open( 'myapp/db/development.sqlite3' )
    my2.results_as_hash = true

    #stmt = my2.prepare("insert into usages ( device_id, bytes_sourced )
    values( ?, ? )" )

    stmt = my2.prepare("UPDATE usages SET bytes_sourced='row[1]' where
    device_id='row[0]'")

    result = my1.query("SELECT device_id, bytes_sourced FROM odma_unit")

    result.each do |row|
    stmt.execute(row[0], row[1])

    my1.close
    end


    When I run the code:

    print print key + ' = ' + value
    print "\n"

    I get a print out to screen of all the items.
    If I run:

    stmt = my2.prepare("insert into usages ( device_id, bytes_sourced )
    values( ?, ? )" )

    I get entries in the database, obviously if i run it again i get
    duplicate entries.

    I just want the code to compare the field device_id in the MYSQL to
    device_id in the SQLite database and thenif there is one to simply
    update the bytes_sourced field. I have tried all sorts of combos for the
    stmt and the script runs without errors but does not insert. Its a hash
    to database entry and I even had the following code:

    result.each do |key, value|
    stmt.execute(key, value)

    I tihnk I have an error in my result statement.Does anyone have any
    advice?

    Thanks

    Gigg
    --
    Posted via http://www.ruby-forum.com/.
    Grant Gray, Jul 11, 2009
    #1
    1. Advertising

  2. Grant Gray

    Roger Pack Guest


    > I tihnk I have an error in my result statement.Does anyone have any
    > advice?


    I've heard some people suggest somehow using active record to go between
    two db's. Never done it myself tho.
    =r
    --
    Posted via http://www.ruby-forum.com/.
    Roger Pack, Jul 12, 2009
    #2
    1. Advertising

  3. Grant Gray

    Thomas Chust Guest

    2009/7/11 Grant Gray <>:
    > [...]
    > I have been trying to get UPDATE SET working to move data from MYSQL to
    > SQLite3.
    > [...]


    Hello,

    I would suggest checking the SQLite3 documentation for the 'INSERT OR
    REPLACE ...' syntax, which may be exactly what you need.

    cu,
    Thomas


    --
    When C++ is your hammer, every problem looks like your thumb.
    Thomas Chust, Jul 13, 2009
    #3
    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. Michael

    MySQL vrs SQLite

    Michael, May 5, 2004, in forum: Python
    Replies:
    14
    Views:
    674
    Richie Hindle
    May 7, 2004
  2. John Salerno

    sqlite query not working

    John Salerno, Nov 7, 2006, in forum: Python
    Replies:
    8
    Views:
    284
    John Salerno
    Nov 8, 2006
  3. Carl Youngblood
    Replies:
    1
    Views:
    210
    Carl Youngblood
    Apr 9, 2005
  4. Replies:
    4
    Views:
    334
  5. Replies:
    3
    Views:
    69
    John Bokma
    Apr 9, 2014
Loading...

Share This Page