Python
Use Python 2 and 3 Pyodbc and Sqlalchemy to connect to SQL Server Client 11.0
There are several ways you can use Python to connect to SQL Server and one of such ways is to
April 18, 2021
No Comments
SQL
Pyodbc Sqlalchemy Python 2 and 3 SQL Server Native Client 11.0
This is a sample code to use Python to connect to SQL Server Native Client.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import sqlalchemy import pyodbc try: import urllib params = urllib.quote_plus("DRIVER={SQL Server Native Client 11.0};SERVER=.\MSSQLSERVER_ENT;DATABASE=MagicDirect;Trusted_Connection=yes;") engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect=%s"' % params) results.to_sql("clusterSegments", engine, if_exists = 'replace')# except (RuntimeError, TypeError, NameError): print('Error in Conneccting') print(RuntimeError, TypeError, NameError) finally: print("connected") |
April 18, 2021
No Comments
Resources
SQLSaturday Manchester 15th July 2017 Event- My Experience
So I was at the SQLSaturday event in Manchester, United Kingdom on the 15th July 2017. According the SQLSaturday website,
July 16, 2017
No Comments
Data Science
Java Python R SQL Excel Compared Similarities For Data Science and Data Analytics – The Basics
If you have ever worked with Java, Python, R, SQL, Excel and other Languages on a varied Data Science or Data
June 9, 2017
No Comments
Data Warehousing
Drop Shipping Ecommerce Database ER Diagram and Conceptual Modeling
In this short article, we look at a sample ERD Diagram which is designed using ER-Assistant software (usage of this
February 18, 2017
No Comments
Entity Relationship Diagram – ERD and Cardinality Symbols Simple Explanations
Understading cardinality notation and being able to explain them will go a long way in your database design and implementation.
December 30, 2016
No Comments
Data Warehousing
SQL Statement Query Clause Evaluation Order – Which Part of SQL Statements Execute First and in What Order?
Which Part of SQL Statements Execute First and in What Order. It is very important to understand which parts of
December 6, 2016
No Comments
Data Warehousing
Free Entity Relationship and Relational Schema Diagram Tool
ERDPLUS One tool I generally use when working on a projects, mini tasks and exploration is the ERDPLUS tool .
November 27, 2016
No Comments
Creating Oracle PL/SQL Tables for Intercollegiate Athletic Database – Data Warehousing
Creating Oracle tables for Intercollegiate Athletic Database. Part of Assignment one of Data Warehousing for Business Intelligence on Coursera
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
--1 CREATE TABLE FACILITY CREATE TABLE FACILITY( FACNO VARCHAR(12), FACNAME VARCHAR(255) NOT NULL, CONSTRAINT PK_FAC PRIMARY KEY (FACNO) ); --2.REATE TABLE EMPLOYEE CREATE TABLE EMPLOYEE( EMPNO VARCHAR(12), EMPNAME VARCHAR(255) NOT NULL, DEPARTMENT VARCHAR(100) NOT NULL, EMAIL VARCHAR(50) NOT NULL, PHONE VARCHAR(30) NOT NULL, CONSTRAINT PK_EMP PRIMARY KEY (EMPNO), CONSTRAINT CHK_EMP CHECK(LENGTHB(PHONE) BETWEEN 5 AND 30) ); --3 RESOURCETBL CREATE TABLE RESOURCETBL( RESNO VARCHAR(12), RESNAME VARCHAR(255) NOT NULL, RATE NUMBER(8,2) NOT NULL, CONSTRAINT PK_RES PRIMARY KEY (RESNO) ); /* ALTER TABLE CUSTOMER drop constraint CHK_CUS_ZIP; ALTER TABLE CUSTOMER ADD CONSTRAINT CHK_CUS_ZIP CHECK(LENGTHB(ZIP) <=10) */ --4 CUSTOMER CREATE TABLE CUSTOMER( CUSTNO VARCHAR(12), CUSTNAME VARCHAR(255) NOT NULL, ADDRESS VARCHAR(255) NOT NULL, INTERNAL CHAR(1) NOT NULL, CONTACT VARCHAR(255) NOT NULL, PHONE VARCHAR(30) NOT NULL, CITY VARCHAR(50) NOT NULL, STATE CHAR(2) NOT NULL, ZIP INTEGER NOT NULL, CONSTRAINT PK_CUS PRIMARY KEY (CUSTNO), CONSTRAINT CHK_CUS CHECK (LENGTHB(PHONE) BETWEEN 5 AND 30), CONSTRAINT CHK_CUS_ZIP CHECK(LENGTHB(ZIP) <=10) ); --5 LOCATION CREATE TABLE LOCATION( LOCNO VARCHAR(12), FACNO VARCHAR(12), LOCNAME VARCHAR(255) NOT NULL, CONSTRAINT PK_LOC PRIMARY KEY (LOCNO), CONSTRAINT FK_LOC FOREIGN KEY (FACNO) REFERENCES FACILITY ); -- 6 EVENTREQUEST CREATE TABLE EVENTREQUEST( EVENTNO VARCHAR(12), DATEHELD DATE NOT NULL, DATEREQ DATE NOT NULL, CUSTNO VARCHAR(12), FACNO VARCHAR(12), DATEAUTH DATE NOT NULL, STATUS VARCHAR(20) NOT NULL, ESTCOST NUMBER(8,2) NOT NULL, ESTAUDIENCE INTEGER NOT NULL, BUDNO VARCHAR(12), CONSTRAINT PK_EVR PRIMARY KEY (EVENTNO), CONSTRAINT FK_EVR_CUS FOREIGN KEY (CUSTNO) REFERENCES CUSTOMER, CONSTRAINT FK_EVR_FAC FOREIGN KEY (FACNO) REFERENCES FACILITY, CONSTRAINT CHK_AUD CHECK(ESTAUDIENCE >= 1), CONSTRAINT CHK_STU CHECK(STATUS IN ('Approved', 'Pending', 'Denied')) ); /** LET'S ALLOW NULL VALUES IN THE AUTHORISED DATE, AS we might not as yet have an authorised date**/ ALTER TABLE EVENTREQUEST MODIFY ( DATEAUTH NULL ); --7 EVENTPLAN CREATE TABLE EVENTPLAN( PLANNO VARCHAR(12), EVENTNO VARCHAR(12), WORKDATE DATE NOT NULL, NOTES VARCHAR(1000) NOT NULL, ACTIVITY VARCHAR(500) NOT NULL, EMPNO VARCHAR(12), CONSTRAINT PK_EVP PRIMARY KEY (PLANNO), CONSTRAINT FK_EVP_EVR FOREIGN KEY (EVENTNO) REFERENCES EVENTREQUEST ); /*ALLOW NULL VALUES IN NOTES COLUMN*/ ALTER TABLE EVENTPLAN MODIFY ( NOTES NULL ); -- 8 EVENTPLANLINE CREATE TABLE EVENTPLANLINE( PLANNO VARCHAR(12), LINENO INTEGER NOT NULL, TIMESTART DATE NOT NULL, TIMEEND DATE NOT NULL, NUMBERFLD INTEGER NOT NULL, LOCNO VARCHAR(12), RESNO VARCHAR(12), CONSTRAINT PK_EVPLL PRIMARY KEY (PLANNO, LINENO), CONSTRAINT FK_EVPLL_EVP FOREIGN KEY (PLANNO) REFERENCES EVENTPLAN, CONSTRAINT FK_EVPLL_LOC FOREIGN KEY (LOCNO) REFERENCES LOCATION, CONSTRAINT FK_EVPLL_RES FOREIGN KEY (RESNO) REFERENCES RESOURCETBL ); |
After creating
November 27, 2016
No Comments
Create Oracle Database Table and Specify Some Constraints
The following are the main constraints which can be defined when you are creating a TABLE. I will be using
November 26, 2016
No Comments
DataPandas LTS
An experienced DevOps and Cloud Training Company to meet your DevOps and Cloud needs