±à¼ÍƼö: |
±¾ÎÄÏêϸ½éÉÜÁËWindowsϵͳÏÂÑÝʾͨ¹ýRODBC°üºÍRMySQL°üʵÏÖÁ¬½Ó32λMySQL£¬²¢¶ÔÊý¾Ý¿â½øÐвé±í¡¢²åÈë±íºÍɾ³ý±íµÈµÄ²Ù×÷µÄÁ½ÖÖ·½Ê½£¬Ï£Íû¶ÔÄúµÄѧϰÓÐËù°ïÖú¡£
±¾ÎÄÀ´×ÔCSDN£¬ÓÉ»ðÁú¹ûÈí¼þAlice±à¼¡¢ÍƼö¡£ |
|
RÖÐÓжàÖÖÃæÏò¹ØϵÐÍÊý¾Ý¿â¹ÜÀíϵͳ(DBMS)µÄ½Ó¿Ú£¬°üÀ¨SQL Server¡¢Access¡¢MySQL¡¢Oracle¡¢DB2µÈ¡£ÆäÖÐһЩ°üͨ¹ýÔÉúµÄÊý¾Ý¿âÇý¶¯À´Ìṩ·ÃÎʹ¦ÄÜ£¬ÁíһЩÔòÊÇͨ¹ýODBC»òJDBCÀ´ÊµÏÖ·ÃÎʵġ£Ê¹ÓÃRÀ´·ÃÎÊ´æ´¢ÔÚÍⲿÊý¾Ý¿âÖеÄÊý¾ÝÊÇÒ»ÖÖ·ÖÎö´óÊý¾Ý¼¯µÄÓÐЧÊֶΣ¬²¢ÇÒÄܹ»·¢»ÓSQLºÍR¡£
Ò»ÖÖ·½Ê½ÊÇÒÀÀµRODBC°ü£¬¸Ã°üʹÓÿª·ÅÊý¾Ý¿âÁ¬½Ó£¨Open Database
Connectivity,ODBC£©Çý¶¯×÷ΪһÖÖÁ¬½Óµ½DBMSµÄ·½·¨£¬Õâ¾ÍÒªÇóÓû§±ØÐëÏÈ°²×°ºÍÅäÖñØÒªµÄÇý¶¯³ÌÐò£¬È»ºó²ÅÄÜÔÚRÖÐʹÓÃËü¡£ÔÚ²»Í¬Æ½Ì¨ºÍºÜ¶àÓÐÖÖDBMS¶¼ÓпÉÓõÄODBCÇý¶¯³ÌÐò¡£ËüÃÇÉõÖÁ»¹ÄÜÕë¶Ô¸ù±¾²»ÊÇÊý¾Ý¿âµÄÊý¾Ý´æ´¢¸ñʽ£¬ÈçCSV»òXLS/XLSX¡£¸Ã×é¼þÒ²ÌṩÁËÒ»Ì×ͨÓ÷½·¨£¬ÀûÓÃͬһ×麯ÊýÀ´¹ÜÀí²»Í¬ÀàÐ͵ÄÊý¾Ý¿â¡£¸Ã·½·¨²»×ãµÄÒ»ÃæÊÇ£¬ËüÒÀÀµÔÚRÔËÐеÄƽ̨ÉÏÊÇ·ñÓÐÄÜÓëÌض¨DBMSÀàÐÍÅäÌ×µÄODBCÇý¶¯³ÌÐò¡£ÁíÒ»ÖÖ·½Ê½ÊÇʹÓÃDBI£¨R
Special Interest Group on Databases 2013£©µÄ×é¼þ£¬ÀýÈçRMySQL¡¢ROracle¡¢RPostgreSQLºÍRSQlite¡£Í¨¹ýËü½¨Á¢µ½Ìض¨DBMSµÄ¡°±¾µØ¡±Á´½Ó¡£DBI×é¼þ¶¨ÒåÁËÐéÄ⺯Êý£¬¶øÕë¶ÔÌض¨Êý¾Ý¿âµÄ×é¼þÔòÕë¶Ô¾ßÌåÊý¾Ý¿âʵÏÖÁËÕâЩº¯Êý¡£ÕâÑùËäÈ»ÓÐһЩͨÓú¯Êý¼¯¶ÔËùÓÐÊý¾Ý¿â¶¼ÊÇͬÑùÓÐЧµÄ£¬¶ø²»Í¬µÄ×é¼þ×÷Õß¿ÉÒÔÖ»Õë¶ÔÒ»ÖÖÀàÐ͵ÄÊý¾Ý¿â½øÐÐÓÐÕë¶ÔÐԵĿª·¢ºÍά»¤¹¤×÷¡£
µ½µ×ʹÓÃÄÄÖÖ·½Ê½ÊµÏÖR·ÃÎÊÊý¾Ý¿â¹ÜÀíϵͳ£¬Õâ´¿´âÒÀÀµÓÚÄã¸öÈ˵ÄÏ°¹ß¡£½ÓÏÂÀ´£¬ÈÃÎÒÃÇÔÚWindowsϵͳÏÂÑÝʾͨ¹ýRODBC°üºÍRMySQL°üʵÏÖÁ¬½Ó32λMySQL£¬²¢¶ÔÊý¾Ý¿â½øÐвé±í¡¢²åÈë±íºÍɾ³ý±íµÈµÄ²Ù×÷¡£
·½Ê½Ò»£ºÍ¨¹ýRODBC°ü·ÃÎÊ32λMySQLÊý¾Ý¿â
MySQLµÄ°²×°·Ç³£¼òµ¥£¨mysql-5.5.28-win32.msi£©£¬´Ë´¦¾Í²»ÔÙ׸Êö¡£°²×°Íê³Éºó£¬ÎÒÃDzâÊÔÏÂÊÇ·ñ°²×°³É¹¦¡£´ò¿ªMySQL
5.5 Command Line Client´°¿Ú£¬ÊäÈëÄã°²×°MySQLʱÉèÖõÄÃÜÂ룬¼´¿ÉµÇ¼MySQL¡£ÈçϽØͼËùʾ£º
ÊäÈëshow databasesÃüÁî²é¿´Ä¿Ç°ÒÑÓеÄÊý¾Ý¿â¡£
¼ÙÈçÎÒÃÇÏë½øÈëmysqlÊý¾Ý¿â£¬ÀûÓÃÀûÓÃusemysqlÃüÁÏë²é¿´mysqlÊý¾Ý¿âÖÐÓÐÄÄЩ±í£¬¿ÉÒÔshow
tablesÃüÁî¡£
ÏÖÔÚ£¬ÈÃÎÒÃÇÊäÈëstatusÃüÁî²é¿´°²×°µÄMySQL°æ±¾¡£
¿É¼û£¬ÎÒ¼ÆËã»ú°²×°µÄÊÇ32λµÄMySQL£¨ÎªÁËÓë·þÎñÆ÷ÉϵÄMySQL°æ±¾Ò»Ö£©¡£´Ëʱ£¬ÎÒÃÇÐèÒªµ÷³ö32λµÄODBCÊý¾ÝÔ´¹ÜÀíÆ÷À´ÅäÖÃMySQLÇý¶¯¡£ÓÉÓÚ±¾»ú¼ÆËã»ú°²×°µÄ64λµÄWin
7£¬ËùÒÔ¿ØÖÆÃæ°åÖеÄODBCÊý¾ÝÔ´¹ÜÀíÆ÷Ò²ÊÇ64λµÄ£¬ÎÒÃÇÐèÒªÔÚC:\Windows\SysWOW64Îļþ¼ÐÏÂÕÒµ½odbcad32.exe£¬Ë«»÷´ò¿ªODBCÊý¾ÝÔ´¹ÜÀíÆ÷½çÃæ¡£
µã»÷Ìí¼Ó£¬µÃµ½ÒÔÏ´°¿Ú£¬Ñ¡ÔñMySQLÇý¶¯£º
Èç¹ûÕÒ²»µ½MySQLÇý¶¯£¬ÏÂÔØmysql-connector-odbc-5.3.6-win32.msiË«»÷½øÐа²×°¼´¿É¡£Ñ¡ÔñMySQLÇý¶¯µã»÷Íê³ÉºóµÃµ½µÄ´°¿ÚÈçÏ£º
ÆäÖÐData Source Name¿ÉÒÔÌîдÄã×Ô¼ºÏ²»¶µÄÃû³Æ£¨Õâ±ß¼ÙÉèÉèÖÃΪdaniel£©£¬Description¿ÉÌî¿É²»ÌTCPIP
ServerÊÇÄãÒªÁ¬½ÓµÄMySQLÊý¾Ý¿âIPµØÖ·£¬Èç¹ûÊÇÄã±¾»ú¼ÆËã»ú£¬¿ÉÌîдlocalhost£¬PortĬÈ϶˿ںÅÊÇ3306£¬UserºÍPasswordÊǵǼMySQLµÄÕ˺źÍÃÜÂ룬DatabaseÊÇÐèÒªÁ¬½ÓMySQLÖеÄÄǸöÊý¾Ý¿â¡£ÉèÖÃÍê³Éºó£¬µã»÷Test°´Å¥ÑéÖ¤ÊÇ·ñÅäÖÃOK¡£
³öÏÖConnectionSuccessful£¬ËµÃ÷Çý¶¯ÅäÖóɹ¦¡£½ÓÏÂÀ´£¬ÎÒÃǾÍÐèÒªÔÚRÖа²×°RODBC°ü£¨Í¨¹ýinstall.packages(¡°RODBC¡±)£©£¬ÊµÏÖR·ÃÎÊÊý¾Ý¿â¹ÜÀíϵͳ¡£´ó¼ÒÐèҪעÒâÒ»µã£¬Èç¹ûÄãÃÇÊÇ64λµÄ¼ÆËã»ú£¬°²×°RʱĬÈÏÊÇ°²×°32λºÍ64λÁ½¸ö°æ±¾µÄ£¬´ËʱÐèÒªÔÚ32λµÄRÖа²×°RODBC°ü¡£
°üÏÂÔØ°²×°ºÃºó£¬¾Í¿ÉÒÔÀûÓðüÖеÄodbcConnect(dsn, uid = "",
pwd = "", ...)º¯Êý½øÐÐÊý¾Ý¿âÁ¬½Ó£¬²¢¼ÌÐøÊý¾ÝµÄ´«Êä¼°·ÖÎö¹¤×÷¡£
>library(RODBC)
> channel <-odbcConnect("daniel","root","123456")
> channel
RODBC Connection 1
Details:
case=tolower
DSN=daniel
UID=root
PWD=****** |
¿ÉÒÔͨ¹ýodbcGetInfoÃüÁî²é¿´Á¬½ÓÊý¾Ý¿âµÄÏêϸÐÅÏ¢¡£
¼ÙÈçÏë°ÑRÖеÄmtcarsÊý¾Ý¼¯±£´æµ½MySQLÖУ¬¿ÉÒÔͨ¹ýsqlSaveÃüÁîʵÏÖ¡£µÚÒ»¸ö²ÎÊýchannelÊǽ¨Á¢µÄÁ´½Ó£¬µÚ¶þ¸ö²ÎÊýdatÊÇÖ¸RÖеÄÊý¾Ý¼¯£¬µÚÈý¸ö²ÎÊýtablenameÊÇÖ¸MySQLÖеıíÃ÷£¬append²ÎÊýÊÇÂß¼Öµ£¬Ä¬ÈÏΪFALSE±íʾ½¨Á¢ÐÂ±í£¨Èç¹û±íÃ÷ÒѾ´æÔÚÔò»á±¨´í£©£¬TRUE±íʾÔÚÒÑÓбíÖвåÈëÐÂÊý¾Ý¡£
ÎÒÃÇÏÖÔÚMySQLÊäÈëdesc mydataÃüÁî²é¿´±ímydataµÄÊý¾Ý½á¹¹£¬²¢Í¨¹ýSQLÓï¾ä²é¿´ÐÐÊý¡£
¿É¼û£¬±ímydataÒѾ´æÔÚ£¬ÇÒÓÐ32Ìõ¼Ç¼¡£ÎÒÃÇÔÚRÖÐÔËÐÐÒÔÏÂÃüÁ
sqlSave(channel,mtcars,"mydata",append=
FALSE)
´Ëʱ±¨´í£¬´íÎóÔÒò¾ÍÊǽ«append²ÎÊýÉèÖÃΪFALSE,ÌáʾMySQLÖÐmydata±íÒѾ´æÔÚ¡£
ÎÒÃǽ«append²ÎÊýÉèÖÃΪTRUEÔٴγ¢ÊÔ¡£
ÔËÐгɹ¦£¬»Øµ½MySQLÖУ¬²é¿´mydataµÄÐÐÊý¡£
ÐÐÊý±È֮ǰ¶àÁËÒ»±¶£¬Ö¤Ã÷Êý¾ÝÒѾÔÚºóÃæ×·¼Ó³É¹¦¡£
ÏÖÔÚ£¬ÎÒÃÇÔÚRÖÐÀûÓÃsqlDropÃüÁMySQLÖеıímydataɾ³ý¡£ÔËÐÐÒÔÏÂÃüÁî
sqlDrop(channel,"mydata")
»Øµ½MySQLÖУ¬Í¨¹ýSQLÓï¾ä²é¿´±ímydataµÄÇ°ÁùÐУº
´íÎóÌáʾ˵Ã÷±ímydata²»´æÔÚ¡£
ÔÙ´ÎÔËÐÐsqlSave(channel,mtcars,"mydata",append=
FALSE)ÃüÁîÖØн«mtcarsÊý¾Ý¼¯±£´æµ½MySQLÖУ¬Éú³Éбímydata¡£ÏÖÔÚÎÒÃÇÔÚRÖÐÀûÓÃsqlFetchÃüÁîºÍsqlQueryÃüÁî½øÐÐMySQL±íµÄÌáÈ¡ºÍSQLÓï¾ä²éѯ¡£
µ±È»£¬ÎÒÃÇÒ²¿ÉÒÔÖ´Ðиü¸´ÔÓµÄSQLÓï¾ä£¬Íê³É¶à±í²éѯ»òÊý¾Ýͳ¼Æ¹¤×÷¡£±ÈÈçÏë¸ù¾ÝvsºÍam±äÁ¿½øÐзÖ×é¼ÆËãmpgµÄƽ¾ùÖµ£¬¿ÉÒÔÖ´ÐÐÒÔÏÂÃüÁ
sqlQuery(channel,"selectvs,am,avg(mpg) from
mydata group by vs,am")
½á¹ûÓëÀûÓÃaggregate(mtcars$mpg,by= list(mtcars$vs,mtcars$am),mean)ÃüÁîÏàͬ¡£
ºÃÁË£¬³£ÓõÄһЩº¯Êý¹¦ÄÜÒѾ½éÉÜÁË£¬×îºó£¬´ó¼Ò±ðÍü¼Çͨ¹ýodbcCloseÃüÁî¹Ø±ÕÁ¬½Ó¡£
>odbcClose(channel)
>odbcGetInfo(channel)
Error inodbcGetInfo(channel) : argument is not
an open RODBC channel |
·½Ê½¶þ£ºÍ¨¹ýRMySQL°ü·ÃÎÊ32λMySQLÊý¾Ý¿â
´Ë´¦»¹ÊÇÒÔWindowsΪÀý½øÐÐÑÝʾ£¨linuxϵͳÀàËÆ£©¡£RMySQL°üµÄ°²×°·Ç³£¼òµ¥£¬²»ÐèÒªÅäÖÃODBCÊý¾ÝÔ´¹ÜÀíÆ÷£¬Ö±½ÓÔÚRÖÐͨ¹ýinstall.packages(¡°RMySQL¡±)Óï¾ä½øÐа²×°¡£
°²×°Íê³Éºó£¬Í¨¹ýdbConnectÃüÁÁ¢RÓëMySQLÊý¾Ý¿âµÄÁ¬½Ó¡£
¿ÉÒÔͨ¹ýdbGetInfoÃüÁî²é¿´Á¬½ÓÐÅÏ¢¡£
ͨ¹ýdbListTablesÃüÁî²é¿´Á¬½ÓÊý¾Ý¿âÖеıíÃû¡£
½á¹ûÓëÖ±½ÓÔÚMySQLÔËÐÐshow tablesÃüÁîÏàͬ¡£
ͨ¹ýdbListFieldsÃüÁî²é¿´Ä³¸ö±íµÄ×ֶΡ£
ͨ¹ýdbReadTableÃüÁî¶ÁÈ¡±í¡£
ͨ¹ýdbGetQueryÃüÁî·µ»Ø²éѯÊý¾Ý¡£
ÓÐʱºò£¬´ó¼Ò¿ÉÄÜ»áÓöµ½ÀûÓÃRMySQL°ü¶ÁÈ¡MySQLÖÐÊý¾ÝÊdzöÏÖÖÐÎÄÂÒÂëÎÊÌâ¡£±ÈÈçÎÒµÄMySQLÖÐÓÐÒ»¸ö½ÐrevenueµÄ±í£¬ÊÕ¼ÁËһЩÓÎÏ·ÊÕÈëµÄÊý¾Ý¡£
Èç¹ûÎÒÃÇÔÚRÖÐͨ¹ýdbGetQueryÃüÁî²éѯ¸Ã±íµÄÇ°ÁùÐÐÊý¾Ý¡£
³öÏÖÖÐÎÄÂÒÂëÎÊÌâ¡£ÎÒÃÇÖ´ÐÐÒÔÏ´úÂë½â¾öÖÐÎÄÂÒÂëÎÊÌâ¡£
¿É¼û£¬ÖÐÎÄÒѾÕý³£ÏÔʾ¡£
ºÃÁË£¬ÒѾ·Ç³£Ïêϸ½éÉÜÁËRODBC°üºÍRMySQL°üµÄ°²×°¼°Ó÷¨£¬´ó¼Ò¿ÉÒÔ¸ù¾Ý×Ô¼ºµÄÏ°¹ßÑ¡Ôñ°üÀ´½øÐг¢ÊÔ¡£
|