some SQL

SQL Server ..

1. Open SQLServer any version whatever you have or visual studio which has SQL Server in it.

2. You might see something as LocalDB , right click on it and select new query , new query tab will        open then you can write "create database <database name>;" it will create a database with the name you mentioned in <database name>.

3. Right click on the localDB and then select refresh you will see your database name.

4. Once you have created a database you need to create a table and populate it. You can refer to w3schools for various SQL queries and syntax for different functionalities.
w3schools - sql

5.  Now once we have an database with name e.g. tweets and table with names say  tweet_table. we might need to use this in some application, for example here we will use it in our Flask App for getting the tweets and name of the person.









6. From above pic you can see how to add instances of SQL Server.
within these instances we have databases and within these databases we have tables.

7. Now what are instances ? you might ask - the first definition from google - An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine independently. 
Or you can read from Microsoft website - link

8. Now you have basic idea of somethings, now when you want to access data from SQL Server table you need to make sure the SQL Server service is running.
How to know this ? -  search services in windows search box and open the one with gear icon then a page will open, search for SQL server if its started well and fine else right click and start.
see below the image

9. Generally we need Six things to connect to a database
Username, Password, Database Name, Host Name (* name of the machine where the db is hosted i guess), Server and Driver Name.
To get Host Name and User Name one can simply run the below Query.

Use <database name>; - database name you should know
select HOST_NAME() as host_name, SUSER_NAME() as user_name;
Now Server Name - when you connect then there it will be written or if you are already connected the see below in red box.

Driver Name
you can search odbc in windows search box click on Data source (ODBC) the go to driver there you will find which is the latest one. Here for me its the SQL Server Native Client 11.0.

** if your server/database is accessible using windows authentication you don't need the username or password for connection (* will show how to connect later), but if you are SQL Server authentication then you need the username and password.

9. Now if there is SQL Server Authentication you will need the username and password.
now either you can create login with:
CREATE LOGIN [testLogin]
WITH PASSWORD = N'testPassword', DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = OFF;

or if you already know the login an password you can use that or if you know the login but now the password you can change the password using below query:
ALTER LOGIN [sa] WITH PASSWORD=N'newPassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

You can find all the logins present for the server in the security tab below

Now you have everything to connect to a SQL Server database using python .. :)


Python Connection ..


1. Open/Create a .py file which you will use for fetching data from the SQL Server and write the below code.

in case of password

import pyodbc

server = '(localdb)\MSSQLLocalDB'

database = 'tweets'

username = 'sa'

password = 'newPassword'

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)

cursor = cnxn.cursor()

in case of no password (simple windows authentication)

import pyodbc

server = '(localdb)\MSSQLLocalDB'

database = 'tweets'

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database)

cursor = cnxn.cursor()

**the id password server name are all my personal you should use yours

2. Going through above code - 
import pyodbc - its a python library/package/modules we are using here to connect to the SQL Server there are many other you might use but different libraries might have different way of connecting to a database, also pyodbc is an external library so you need to install 
it using pip install or if you are using pycharm then go to settings then project interpreter.
you can read about how to install python packages from google.
pip install