# MySQL to SQlite data movement

**URL:** <https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299>\
**Category:** Ruby\
**Created:** [June 30, 2009, 8:39am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299 "2009-06-30T08:39:15Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [June 30, 2009, 8:39am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299/1 "2009-06-30T08:39:15Z")

</div>

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::Database.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

---

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [June 30, 2009, 10:36am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299/2 "2009-06-30T10:36:43Z")

</div>

Grant G. wrote:

> #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.

---

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [June 30, 2009, 1:09pm UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299/3 "2009-06-30T13:09:22Z")

</div>

Grant G. wrote:

> 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::Database.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::Database.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

---

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [June 30, 2009, 4:59pm UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299/4 "2009-06-30T16:59:33Z")

</div>

Damjan R. wrote:

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

> my = Mysql.real\_connect(“server”, “username”, “password”, “cnms”)  
> db = SQLite3::Database.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

---

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [June 30, 2009, 10:36pm UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-data-movement/171299/5 "2009-06-30T22:36:32Z")

</div>

Grant G. 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::Database.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