Databases
StackOverflow2013
Postsdbo
POHandling field types in database interaction with R
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHandling field types in database interaction with R
    primarykey
    data
    text
    <p>I use RMySQL and a MySQL database to store my datasets. Sometimes data gets revised or I store results back to the database as well. Long story short, there is quite some interaction between R and the database in my use case. </p> <p>Most of the time I use convenience functions like <code>dbWriteTable</code> and <code>dbReadTable</code>to write and read my data. Unfortunately these are just completely ignoring R data types and the MySQL field types. I mean I would expect that MySQL date fields end up in a <code>Date</code> or <code>POSIX</code> class. The other way around I´d think that these R classes are stored as a somewhat corresponding MySQL field type. That means a date should not be character – I do not expect to distinguish between float and doubles here... </p> <p>I also tried to use <code>dbGetQuery</code> – same result there. Is there something I have completely missed when reading the manual or is it simply not possible (yet) in these packages? What would by a nice work around?</p> <p>EDIT: @mdsummer I tried to find something more in the documentation, but found only these disappointing lines: `MySQL tables are read into R as data.frames, but without coercing character or logical data into factors. Similarly while exporting data.frames, factors are exported as character vectors.</p> <p>Integer columns are usually imported as R integer vectors, except for cases such as BIGINT or UNSIGNED INTEGER which are coerced to R's double precision vectors to avoid truncation (currently R's integers are signed 32-bit quantities).</p> <p>Time variables are imported/exported as character data, so you need to convert these to your favorite date/time representation.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMatt Bannert
    1. USMatt Bannert
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHandling field types in database interaction with R
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHandling field types in database interaction with R
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHandling field types in database interaction with R
      1. USJoris Meys
      1. VTFavorite
    1. COHave you exhausted the documentation for those functions? There's quite a bit of detail in regards to "field.types" for the read and write functions, and how these are inferred using dbDatatype.
      singulars
      1. POHandling field types in database interaction with R
      1. USmdsumner
    2. CO"exhausted" well put. I doubt that I really really have done that, but a good pointer often helps to do better at that. thx!
      singulars
      1. POHandling field types in database interaction with R
      1. USMatt Bannert
    3. COAny chance of a concrete example with some code?
      singulars
      1. POHandling field types in database interaction with R
      1. USSpacedman
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload