[R-sig-DB] adding to a MySQL database from within R?
Spencer Graves
@pencer@gr@ve@ @end|ng |rom @tructuremon|tor|ng@com
Tue Oct 12 16:32:42 CEST 2010
Hi, Dirk:
Thanks for the reply. That gives me search terms for how to look
for more information and encourages me to try first with the MySQL / DBI
solution that I think I almost have working.
Best Wishes,
Spencer
On 10/12/2010 4:24 AM, Dirk Eddelbuettel wrote:
> 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
> |>
>
--
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
More information about the R-sig-DB
mailing list