Tuesday, March 7, 2017

Python MySQL Database Connection using ODBC

I'm posting a sample python to Sql Server connection code. Here i will be using Python 2.7.

1. pip install pyodbc

2. Install the MySQL ODBC Connector from here.

3.  Setup ODBC connection details in Control Panel --> Admin Tools --> System DSN




4. Code :

from os import getenv
import pyodbc

conn = pyodbc.connect(r'Driver={MySQL ODBC 5.3 ANSI Driver};Server=127.0.0.1;Database=test;Trusted_Connection=yes;')

cur = conn.cursor()
string = "CREATE TABLE IF NOT EXISTS TestTable(empname varchar(15), sal double)"
cur.execute(string)

sqlStatement = "INSERT INTO TestTable(empname,sal)  VALUES (?, ?)"

cur.execute(sqlStatement, ('chandan', '1000'))

conn.commit()

4.Run python code.py in CMD



Monday, March 6, 2017

Python MSSQL Database Connection using ODBC

I'm posting a sample python to Sql Server connection code. Here i will be using Python 2.7.

1. pip install pyodbc

2. Code :

from os import getenv
import pyodbc


conn = pyodbc.connect(
    r'DRIVER={ODBC Driver 11 for SQL Server};'
    r'SERVER=TEST;'
    r'DATABASE=SampleDB;'
    r'UID=sa;'
    r'PWD=***'
    )

cursor = conn.cursor()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
""")
cursor.executemany(
    "INSERT INTO persons VALUES (?, ?, ?)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike h', 'Sarah H')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

cursor.execute('SELECT * FROM persons WHERE salesrep=?', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()
k
conn.close()

3.Run python code.py in CMD




Wednesday, January 13, 2016

MongoDB Query to find record using greater $gt or less than $lt operators and then sort

MongoDB Query to find record using greater $gt or less than $lt operators and then sort

Query
db.grades.find({"score":{$gt:65}},{"score":1,student_id:2,}).sort({"score:1})

grades is the collection.

Tuesday, September 22, 2015

sample interface for inserting data into mongodb using node.js

1. Below is the npm module(mongodb) link which i used to create a basic interface for inserting
documents into mongodb collections using node.js

https://www.npmjs.com/package/mongodb

2. I have been using RoboMongo GUI for viewing into MongoDB Database.

Steps:

1. install node.js and mongodb 
2. create a new folder and access it thru terminal/Cmd
3. Now type npm init, it will ask you certain things and will create a package.json based on your input values.
4. mention the required dependencies for mongodb in package.json file.
5. Create a new file called index.js , and paste this below code
6. final step to run the node.js interface from the terminal type --> node index.js 

package.json

{
  "name": "mongoapp",
  "version": "1.0.0",
  "description": "new mongo",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "mongo"
  ],
  "author": "chandan",
  "license": "ISC",
   "dependencies": {
    "mongodb": "~2.0"
  },
}


index.js

var MongoClient = require('mongodb').MongoClient
  , assert = require('assert');
 
// Connection URL 
var url = 'mongodb://localhost:27017/mydb';
// Use connect method to connect to the Server 
MongoClient.connect(url, function(err, db) {
  assert.equal(null, err);
  console.log("Connected correctly to server");
  insertDocuments(db,logStuff);
});

function logStuff(result)
{
console.log(result);
}

var insertDocuments = function(db, callback) {
  // Get the documents collection 
  var collection = db.collection('documents');
  // Insert some documents 
  collection.insert([
    {a : 1}, {a : 2}, {a : 3}
  ], function(err, result) {
    assert.equal(err, null);
    assert.equal(3, result.result.n);
    assert.equal(3, result.ops.length);
    console.log("Inserted 3 documents into the document collection");
    callback(result);
  });
}

Saturday, August 15, 2015

Executing JAR file using Batch Command(.bat)

Below is the script to run a JAR file using a .bat command, copy and paste it into the notepad and then save as batch_file_name.bat

@echo OFF
cd C:\upload
java -jar compareRun.jar
pause

line 2 is the folder path for jar file, please change as per your requirements.
line 3 is the actual execution of the jar file
line 4 would avoid closing of console, so you can view the output.