MySQL to SQlite data movement

[Grant Gray]

Hi,

I hope someone can help. I am new to ruby and struggling with a hash and
some database connections. What I need to do is copy data from table in
a mysql database (2 columns), into a sqlite database (2 columns). I can
take the data out of the mysql database with this command:

my = Mysql.real_connect("server", "username", "password", "cnms")
##first db
db = SQLite3:atabase.open( 'simo/db/development.sqlite3' ) ##second db
myresult = my.query("SELECT device_id, bytes_sourced FROM odma_unit")

I then put it into a hash like this:

myresult.each_hash do |row|

row.each do |key, value|

end
If I run a print command on it it spits out the correct data.

If i then insert into sqlite3 with the following commands:

stmt = db.prepare("insert into usages ( device_id, bytes_sourced )
values( 'key', 'value' )" )
stmt = stmt.execute

my.close
#db.close ##this throws out an error
end

This inserts key and value in the correct columns and does the correct
amount of record entries.

Can some put me out of my misery and tell me where I am going wrong. I
know key and value in the sqlite3 statement may need to be variables,
but i am not sure what to make them. As you can see I am still new to
ruby so I am lost as to what i do and the hundreds of websites i have
read don't clear the mist in my brain.

Any help is really, really, really appreciated.

Thanks

Gigg

 

[Brian Candler]

#db.close ##this throws out an error

This inserts key and value in the correct columns and does the correct
amount of record entries.

So what exactly is your problem? That the above line throws an error?
I'm afraid that if you don't show us either the actual error you get,
nor a complete program which replicates the problem, then we can't help
you.

If the problem is due to sqlite3 insert only, then it should be possible
for you to boil this down to a simple test program which generates the
same error, without using mysql. e.g.

my_data = [{"foo" => "bar", "baz" => "bap"}]
... now open a sqlite3 database, prepare, insert, close
... do you get the same error?

If you can do that, and post here an entire standalone program which
demonstrates the problem, then (a) you may solve your problem in the
process of boiling it down, and (b) if you don't, it will be much, much
easier for someone else to help you, as they can just paste your program
to replicate the problem.

As a separate point: you might find it easier to use an abstraction
layer on top of mysql and sqlite3 so that you are using the same API to
access both. ruby-dbi would give you a basic low-level compatibility for
sending SQL. Sequel, Datamapper or ActiveRecord are higher-level
alternatives, if you want to abstract away the SQL generation too.

 

[Damjan Rems]

Hi,

I hope someone can help. I am new to ruby and struggling with a hash and
some database connections. What I need to do is copy data from table in
a mysql database (2 columns), into a sqlite database (2 columns). I can
take the data out of the mysql database with this command:

my = Mysql.real_connect("server", "username", "password", "cnms")
##first db
db = SQLite3:atabase.open( 'simo/db/development.sqlite3' ) ##second db
myresult = my.query("SELECT device_id, bytes_sourced FROM odma_unit")

I then put it into a hash like this:

myresult.each_hash do |row|

row.each do |key, value|

end
If I run a print command on it it spits out the correct data.

If i then insert into sqlite3 with the following commands:

stmt = db.prepare("insert into usages ( device_id, bytes_sourced )
values( 'key', 'value' )" )
stmt = stmt.execute

my.close
#db.close ##this throws out an error
end

This inserts key and value in the correct columns and does the correct
amount of record entries.

Can some put me out of my misery and tell me where I am going wrong. I
know key and value in the sqlite3 statement may need to be variables,
but i am not sure what to make them. As you can see I am still new to
ruby so I am lost as to what i do and the hundreds of websites i have
read don't clear the mist in my brain.

Any help is really, really, really appreciated.

Thanks

Gigg

I guess you aldo have some problems with SQL too ;-)

my = Mysql.real_connect("server", "username", "password", "cnms")
db = SQLite3:atabase.open( 'simo/db/development.sqlite3' ) ##second db
stmt = db.prepare("insert into usages ( device_id, bytes_sourced )
values( ?, ? )" )
myresult = my.query("SELECT device_id, bytes_sourced FROM odma_unit")
myresult.each_hash do |row|
row.each do |key, value|
stmt.execute(key, value)
end
end

But maybe:
myresult.each do |row|
stmt.execute(row['device_id'], row['bytes_sourced')
end

would be better. I am not clear what did you want to do with all the
hashing.


by
TheR

 

[Grant Gray]

Damjan Rems wrote:


Thanks for your guidance so far. There is some progress as below:

my = Mysql.real_connect("server", "username", "password", "cnms")
db = SQLite3:atabase.open( 'simo/db/development.sqlite3' ) ##second db
stmt = db.prepare("insert into usages ( device_id, bytes_sourced )
values( ?, ? )" )
myresult = my.query("SELECT device_id, bytes_sourced FROM odma_unit")
myresult.each_hash do |row|
row.each do |key, value|
stmt.execute(key, value)
end
end

The above code does the following:
device_id 00:80:c6:e8:c6:ae
bytes_sourced 510807

So, its taking a key and value and adding them in row after row until
its done. Something is right but something is not working.

But maybe:
myresult.each do |row|
stmt.execute(row['device_id'], row['bytes_sourced')
end

the above code gives the error:

dbadd.rb:38:in `[]': can't convert String into Integer (TypeError)

The first statement seems to have it almost right. What I am trying to
do is get the device_id and the bytes_sourced into the same row. So, to
simplify, each device_id has a bytes_sourced. Each of these will make a
row.

I am sure I could rewrite this some other way. But, I know a bit about
MySQL and I am trying to understand Ruby and Sqlite3 as well. In time I
plan to get this app in a better format, but for now I just want to get
it working until I have grown in knowledge.

Thanks for the help so far.

Gigg

 

[Grant Gray]

Grant Gray wrote:
Hi Guys,

I solved the problem. I did a few syntax changes to clean up the code
but here it is:

#!/usr/bin/env ruby

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

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

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

result = my1.query("SELECT device_id, bytes_sourced FROM odma_unit")
result.each do |row|
stmt.execute(row[0], row[1])

my1.close
end

The problem seemed to lay in the results_as_hash = true wrapper. "If you
do this, then all rows will be returned as Hash objects, with the column
names as the keys." Once it dawned on me that it was not getting the key
right, the code I used worked.

Thanks for the guidance again. I couldn't have done it without your
help.

Gigg