[R-sig-DB] adding to a MySQL database from within R?
Dirk Eddelbuettel
edd @end|ng |rom deb|@n@org
Tue Oct 12 13:24:18 CEST 2010
On 11 October 2010 at 20:34, Spencer Graves wrote:
| Hi, Dirk, et al.:
|
|
| Thanks for the quick reply.
|
|
| What are the strengths and weaknesses of using RODBC vs. RMySQL?
That is also a question which have been debated to death before on the
Intertubes or this list. Do some searches.
In short, and as I see it,
- ODBC is more generic so what you do here may work similarly with other
software providing ODBC interfaces.
- Specific binary interface can be faster, sometimes by a lot.
- For R in particular, RMySQL supports the DBI interface so here the
situation is the inverse of the general solution: you may use what you
did with RMySQL again with RSQLite or RPostgreSQL or ...
| I don't have either doing what I want yet, but I think I'm closer
| with RMySQL than RODBC. On the other hand, I had the impression that
| RODBC was better developed than RMySQL and therefore RODBC might be
| faster and ultimately easier.
Unsure. They are both exceedingly mature packages that have been around well
over half a decade. From what I understand RMySQL has its headaches with
different MySQL versions as well as Windows builds -- but I never had an
issue.
Dirk
|
| Thanks again.
| Spencer Graves
|
|
| On 10/11/2010 8:14 PM, Dirk Eddelbuettel wrote:
| > On 11 October 2010 at 20:00, Spencer Graves wrote:
| > |
| > | How can I connect to a MySQL database with RODBC under Vista? I
| > | tried the following, inspired by Ripley's RODBC vignette:
| > |
| > |
| > |> MySQLcon<- odbcConnect('fillmoredb', uid='sbg', pwd='********')
| > | Warning messages:
| > | 1: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********) :
| > | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
| > | Manager] Data source name not found and no default driver specified
| > ^^^^^^^^^^^^^^^^^^^^^^^^^^
| >
| > You need to define a DSN named 'fillmoredb'. Windows being Windows, there
| > are countless tutorials on the net about how to do this using the admin tools.
| >
| > The nice thing is that process is just about the same for every 'source'
| > providing ODBC connectivity, so once you grok this you can re-use the same
| > trick for non-MySQL-stored data.
| >
| > The not-so-nice thing, in my book, is that you still need to install the odbc
| > drivers and beed to set up the dsn on each machine trying to use it.
| >
| > Dirk
| >
| > | 2: In odbcDriverConnect("DSN=fillmoredb;UID=sbg;PWD=********") :
| > | ODBC connection failed
| > |> MySQL.<- odbcConnect('MySQL')
| > | Warning messages:
| > | 1: In odbcDriverConnect("DSN=MySQL") :
| > | [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
| > | Manager] Data source name not found and no default driver specified
| > | 2: In odbcDriverConnect("DSN=MySQL") : ODBC connection failed
| > |
| > |
| > | Thanks,
| > | Spencer Graves
| > |
| > |
| > |> sessionInfo()
| > | R version 2.11.1 (2010-05-31)
| > | i386-pc-mingw32
| > |
| > | locale:
| > | [1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United
| > | States.1252 LC_MONETARY=English_United States.1252
| > | [4] LC_NUMERIC=C LC_TIME=English_United
| > | States.1252
| > |
| > | attached base packages:
| > | [1] splines stats graphics grDevices utils datasets methods
| > | base
| > |
| > | other attached packages:
| > | [1] RODBC_1.3-2 multcomp_1.2-3 survival_2.35-8 mvtnorm_0.9-92
| > |
| > | loaded via a namespace (and not attached):
| > | [1] tools_2.11.1
| > |
| > | ####
| > | Enter password: ********
| > | Welcome to the MySQL monitor. Commands end with ; or \g.
| > | Your MySQL connection id is 7
| > | Server version: 5.1.50-community MySQL Community Server (GPL)
| > |
| > | Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
| > | This software comes with ABSOLUTELY NO WARRANTY. This is free software,
| > | and you are welcome to modify and redistribute it under the GPL v2 license
| > |
| > | Type 'help;' or '\h' for help. Type '\c' to clear the current input
| > | statement.
| > |
| > | mysql>
| > |
| > | ########################
| > | Hi, Sean and Jack Tanner:
| > |
| > |
| > |
| > | It's hard to RTFM when I don't know which FMTR. Thanks to you
| > | both for helping me overcome this problem.
| > |
| > |
| > | 1. Before I submitted this question, I read the help page
| > | for "dbWriteTable". Unfortunately, I missed the "append" argument. In
| > | this case, I RTFM without seeing what was there!
| > |
| > |
| > | 2. Jack's suggestion led me to try "RSiteSearch('MySQL
| > | INSERT')". This led me to a vignette written by Brian Ripley for the
| > | RODBC package. I tried other searches without getting an answer. Also,
| > | I had previously seen a post where Ripley recommended the RODBC package.
| > | However, my previous searched did not help me figure out how to get
| > | started with it.
| > |
| > |
| > | Best Wishes,
| > | Spencer
| > |
| > |
| > | On 10/9/2010 5:47 AM, Sean Davis wrote:
| > |> On Sat, Oct 9, 2010 at 12:00 AM, Spencer Graves<
| > |> spencer.graves using structuremonitoring.com> wrote:
| > |>
| > |>> Hello, All:
| > |>>
| > |>>
| > |>> How can I add rows to a MySQL database?
| > |>>
| > |>>
| > |>> I can do it manually within MySQL server 5.1.50-community. I can
| > |>> connect to it using the RMySQL package. When I tried "dbReadTable
| > |>> {DBI}", I got a warning that "table headers exists:
| > |>>
| > |>>
| > |>> > MySQL.<- MySQL()
| > |>> > MySQLcon<- dbConnect(MySQL., user=User, password=pw, dbname=myDB)
| > |>> > dbWriteTable(MySQLcon, 'headers', a.data.frame)
| > |>> Warning message:
| > |>> In mysqlWriteTable(conn, name, value, ...) :
| > |>> table headers exists in database: aborting mysqlWriteTable
| > |>>
| > |>>
| > |>> I know that table "headers" exists: I want to add to it, not
| > |>> create the entire table from scratch at once.
| > |>>
| > |>>
| > |> See the help for dbWriteTable(). In particular, check out the append
| > |> parameter.
| > |>
| > |> Sean
| > |>
| > |
| > |
| > | --
| > | Spencer Graves, PE, PhD
| > | President and Chief Operating Officer
| > | Structure Inspection and Monitoring, Inc.
| > | 751 Emerson Ct.
| > | San José, CA 95126
| > | ph: 408-655-4567
| > |
| > | _______________________________________________
| > | R-sig-DB mailing list -- R Special Interest Group
| > | R-sig-DB using stat.math.ethz.ch
| > | https://stat.ethz.ch/mailman/listinfo/r-sig-db
| >
--
Dirk Eddelbuettel | edd using debian.org | http://dirk.eddelbuettel.com
More information about the R-sig-DB
mailing list