DB Connections

In [ ]:
# sqlserver
from sqlalchemy import *
from pandas import *
engine = create_engine('mssql://sql_server_dsn_32')
conn = engine.connect()
rs = conn.execute("select * from table1")
df = DataFrame(rs.fetchall())
print(df)
rs.close()
In [ ]:
import pyodbc
ServerName="server1\instance1"
Database="db1"
#engine=create_engine('mssql+pyodbc://' + ServerName + '/' + Database)
engine=sa.create_engine('mssql+pyodbc://' + ServerName + '/' + Database + '?driver=ODBC+Driver+11+for+SQL+Server')
df=pd.read_sql_query("select * from table1", engine)
In [ ]:
#msaccess
import pyodbc
conn = pyodbc.connect(strConnection)
In [ ]:
import pyodbc
#conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Prakriti\Learning\Python\Tutorial_WH.mdb')
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:\Prakriti\Learning\Python;DBQ=C:\Prakriti\Learning\Python\Tutorial_WH.mdb')
cursor = conn.cursor()
for table_name in cursor.tables(tableType='TABLE'):
    print(table_name)
In [ ]:
#con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:\Prakriti\Learning\Python;DBQ=C:\Prakriti\Learning\Python\Tutorial_WH.mdb')
#cursor = con.cursor()
cursor.execute("SELECT * FROM LU_SUPPLIER")
for row in cursor.fetchall():
    print(row.SUPPLIER_NAME)
In [ ]:
#hadoop
from pyspark import RDD, SQLContext, HiveContext
df = sqlContext.sql(strSql)

# First step : Please/Load file in Hadoop/HDFS
rdd = sc.textFile('/tmp/rew1602_tgtofferdat.csv')

from pyspark.sql import Row
#extract header
header = rdd.first() 
print header
rdd = rdd.filter(lambda line:line != header)
parts = rdd.map(lambda l: l.split(","))
#parts.collect()
data = parts.map(lambda p: Row(upccd=p[0], dib_prod_id=p[1],dib_prod_code=p[2],dib_prod_code_30=p[3],
                               dib_prod_code_40=p[4],sku=p[5],ccid=p[6],catflag=p[7],tgtflag=p[8]))
schemaCluster = sqlContext.createDataFrame(data)
schemaCluster.printSchema()
#Register dataframe as temp table
schemaCluster.registerTempTable("temp_ccid_final2")
#Create table with model equations
sqlContext.sql('drop table if exists ccid_final2')
sqlContext.sql("""create table ccid_final2
                as select * from temp_ccid_final2""")
www.000webhost.com