I'm hoping to find a unique address identifier from a SQL table. The table on SQL server is too large for me to pull it into R. I have the connection set up, but I don't know how to query each row of the table that I have in R according to each column.

So I have one table loaded into R that has postcode, street, house number etc.

In SQL I have the same table, but with a unique identifier that is missing from what I have in R.

How can I pull just the unique identifiers into my table in R?

Below is one line of how it looks on SQL Server, in R I have the same table but without the UDPRN code. How can I get UDPRN into my R data for each row?

CodePudding user response：

My apologize,R should:

library(tidyverse)
library(RMariaDB)
library(DBI)

conn <- dbConnect(RMariaDB::MariaDB(), 
                  host = 'xxx.xxx.xxx.xxx',
                  port = 3306,
                  user = 'xxxxxx',
                  password = 'xxxxxx',
                  dbname = 'high_school')
dbListTables(conn)

res <- dbSendQuery(conn, "SELECT * FROM exam WHERE stud_id = 21")

dbDisconnect(conn)

CodePudding user response：

Just an example that I fetch one student record from MySQL database. which the stud_id is a unique key. import pymysql import pandas as pd from pandas import Series

def conn_mysql(conn_arg, qry):
    try:
        conn = pymysql.connect(**conn_arg)
        result = pd.read_sql(qry, conn)
        return result

    except ConnectionRefusedError:
        print('connect to MySQL failed.')

    finally:
        conn.close()

def main():
    conn_arg = {
        'host':         'xxx.xxx.xxx.xxx',
        'port':         3306,
        'user':         'xxxxxx',
        'password':     'xxxxxx',
        'charset':      'utf8mb4',
        'use_unicode':  True
        }
    qry = ('''SELECT * FROM high_school.exam where stud_id = 21''')
    result_set = conn_mysql(conn_arg, qry)    

if __name__ == '__main__':
    main()