How Python Connect to, and Manage a Database

If you’ve been trying to connect to a database on-premise or on a local disk, and found ambiguous online resources and inconsistent terminology, then you will enjoy this article

I’ve put my best effort to provide you with a clear, concise, and detailed description on how to connect to, and manage any database from Python
1. Introduction

2. PyODBC: Open DataBase Connectivity for Python

3. SQLite3: SQLite for Python

4. Executing SQL statements

5. Reading data into Pandas DataFrame

TL;DR: full code

1. Introduction
A database model determines the logical structure of a database (a database is an organized collection of data). This in turn determines how data can be stored, organized and manipulated. The Relational Model (RM) is the most popular database model since the 1980s. RM uses a table-based format, where tables are related by common columns

Database management system (DBMS)

DBMS is the software that you -or applications- use to interact with the database to create, read, update and manage data. The Relational DBMS (RDBMS) is the DBMS based on RM. According to DB-Engines, the most widely used RDBMS are: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM DB2, Microsoft Access, and SQLite

A database is often referred to by the DBMS used to manipulate it. For example, a database that is manipulated using Microsoft SQL Server is referred to as: Microsoft SQL Server-Database
Although not required, all RDBMS use SQL as a standard data access language

Database connectivity interface

A database connectivity interface allows an application to access data from a variety of DBMSs, using a specific driver for a specific DBMS and operating system. This means that the application can be written without depending on a specific DBMS or the operating system

Open DataBase Connectivity (ODBC) is a standard Microsoft Windows interface that enables applications (typically written in C or C++) to connect to DBMSs. While, Java DataBase Connectivity (JDBC) is a standard Oracle interface that enables applications written in Java to connect to DBMSs

2. PyODBC: Open DataBase Connectivity for Python
PyODBC is the Python package for ODBC. Through pyodbc, you can easily connect Python applications to a DBMS-database, using the required ODBC driver

Installation
Step 1: install pyodbc through pip

pip install pyodbc
Step 2: install the required driver for the DBMS-database you want to connect to. For example, if you want to connect to a Microsoft SQL Server-Database, you need to download and install the driver from Microsoft, after choosing your operating system

Connecting to a database

To make a connection to a database, we need to pass a connection string to the connect() function of pyodbc. The most important thing to remember is that: pyodbc passes the connection string directly to the DBMS-database driver unmodified. Therefore, connection strings are driver-specific

For example, to connect to a Microsoft SQL Server-Database, we provide the following connection string:


Note: you can find information about general connection string for most databases here. Make sure to follow the connection string formatting rules found here
The connection string can also be passed as arguments, which are then concatenated into a connection string:


In the connection string above, we assume that the SQL Server Authentication mode is set to Windows Authentication Mode, so we did not have to provide credentials to get access permissions to the server.

However, if it was a Mixed Authentication Mode (i.e. SQL Server Authentication + Windows Authentication), we can still use the above connection string, or the following one where we provide our User ID and Password to get access permissions to the server:


Pyodbc also supports Data Source Name connections. A DSN contains information about a specific database that an ODBC driver needs in order to connect to it (information such as the database driver, the server and database names, etc):


Lastly, depending on the database you are trying to access, and the version of Python you are using, you might need to set the connection encoding/decoding settings, which you can find here

For Microsoft SQL Server-Database and Python 3.x, using pyodbc default encoding/decoding settings is recommended (i.e. no need to do anything)

3. SQLite3: SQLite for Python
SQLite3 is the Python package for SQLite, which is the most widely deployed RDBMS in the world (arguably). The main features of SQLite are:

An embedded database. SQLite does not have a separate server process (i.e. server-less). It reads and writes directly to ordinary disk files
Free for use for any purpose, commercial or private
Installation

SQLite3 is included with Python by default. So even if you have created a new Anaconda environment, it will be installed for you

Connecting to a database

To create a connection to a disk-based database, we just need to pass the name of the database to the connect() function of sqlite3:


If the database does not exist, an empty one will be created for us

4. Executing SQL statements
Once we have a connection to the database, either from pyodbc or sqlite3, we can then create a Cursor object which represents a database cursor. We can do this using the cursor() method:


Now we have a Cursor object, we can execute any valid SQL query string using the Cursor execute() method:


Let’s assume that the database we are connected to (database.db), has 2 tables:

T_CUSTOMERS

T_ADDRESSES

SELECT statement

To select the id, fname, and lname columns from T_CUSTOMERS, we use SQL SELECT:


The executed SELECT statement returns rows, which we can retrieve using one of the Cursor fetch functions:

fetchone(): retrieves one row only, and move the Curser to the next row
fetchall(): retrieves all rows, and move the Curser to the end
fetchmany(size): retrieves a number of rows, and move the Curser to the next row
Note: if there are no rows left, fetchone() will return None, whereas fetchall() and fetchmany() will both return empty lists
For example, to retrieve one row only:


row will be a Tuple of values, one for each of the columns in the SELECT statement:


To get each column’s description, the Cursor description attribute can be used:


This description will be a list of Tuples, one for each column. Each Tuple has 7 items to describe the column, the one that matters to us is the first item, which is the column’s name (or alias, if specified in the SQL SELECT):


To retrieve all the remaining rows as a list, we can use fetchall():


UPDATE statement

To update an existing record in T_CUSTOMERS, we use SQL UPDATE with a WHERE clause

Note: if you do not use a WHERE clause in the UPDATE statement to specify which record(s) should be updated, all records will be updated!
Let’s update lname=’Trujillo’ to ‘Taqueria’:


If we want to know how many records were modified by the last SQL statement, we can use the Cursor rowcount attribute. This will return -1 if no SQL has been executed or if the number of rows is unknown:


Let’s check if that record has been updated successfully:


DELETE statement

To delete a customer’s record from T_CUSTOMERS, we use SQL DELETE with a WHERE clause

Note: if you do not use a WHERE clause in the DELETE statement to specify which record(s) should be deleted, all records will be deleted!
Let’s delete the record with id=4:


Let’s check how many records were modified:


Let’s check if that record has been deleted successfully:


INSERT INTO statement

To insert a new record into T_CUSTOMERS, we use SQL INSERT INTO

Let’s insert the same record we have deleted:


Let’s check how many records were modified:


Let’s check if that record has been inserted successfully:


Long SQL statement

Let’s say that we want to select the full name, and city and postal code of a customer with the last name ‘Trujillo’. We (or someone else) has written the following SQL statement to do this:


The easiest way to pass this long SQL statement to excute() is to use the triple-quote string format to encapsulate the statement. White-spaces (including tabs and newlines) should be ignored by the SQL database engine, but if we want to remove them from the left, we can use the dedent() function from the built-in textwrap module:


Finally, we must call commit() on the connection that created this cursor, otherwise our changes will be lost if we close the Script:


5. Reading data into Pandas DataFrame
Pandas can read an SQL statement directly into a dataframe without using a Cursor. This can be done using the read_sql(sql_string, connection) function

Everything you need to know about Scatter Plots for Data Visualisation

In case you’re a statistics Scientist there’s absolute confidence which you’ve worked with scatter plots before. Despite their simplicity, scatter plots are a powerful device for visualizing statistics. There’s numerous options, flexibility, and representational power that comes with the simple alternate of some parameters like color, length, form, and regression plotting.

Right here you’ll study pretty much the whole lot you need to know approximately visualizing facts with scatter plots! We’re going to undergo all of the parameters and see when and how to use them with code. You would possibly simply find a few exceptional surprises and hints that you can upload for your data technology toolbox!

Regression plotting
whilst we first plot our information on a scatter plot it already gives us a pleasant brief evaluate of our information. Within the some distance left parent underneath, we can already see the businesses in which maximum of the records appears to bunch up and can quick pick out out the outliers.

But it’s additionally great with the intention to see how complex our mission would possibly get; we will do that with regression plotting. In the center discern below we’ve achieved a linear plot. It’s pretty clean to peer that a linear function received’t paintings as a few of the points are quite a long way away from the road. The a long way-proper characteristic makes use of a polynomial of order four and looks lots greater promising. So it looks as if we’ll truly want some thing of at least order four to version this data-set.

Coloration and form
color and form may be used to visualize the extraordinary classes on your data-set. Color and shape are each very intuitive to the human visual gadget. While you look at a plot wherein agencies of points have extraordinary coloration's our shapes, it’s quite apparent right away that the factors belong to one of a kind businesses. It just naturally makes feel to us. This herbal intuition is constantly what you need to be playing off of while developing clean and compelling records visualizations. Make it so obvious that it’s self-explanatory.

The discern at the left below suggests the instructions being grouped via color; the discern at the right shows the lessons separated by way of each color and shape. In both instances it’s a great deal simpler to look the groupings than whilst we simply had all blue! We now recognize that it’ll in all likelihood be easy to split the Samoset class with low errors and that we need to attention our interest and identifying how to separate the alternative  from each different. It’s additionally clear that a unmarried linear plot won’t be able to separate the green and orange points; we’ll need some thing a bit greater excessive-dimensional.

Selecting between color and shape becomes a depend of preference. Individually, I locate colour a chunk greater clean and intuitive, but take your choose!

Marginal Histogram
Scatter plots with marginal histograms are the ones that have plotted histograms at the top and side, representing the distribution of the points for the functions along the x- and y- axes. It’s a small addition however tremendous for seeing the exact distribution of our points and more accurately discover our outliers.

As an instance, inside the discern under we can see that the why axis has a totally heavy concentration of points around three.Zero. Just how concentrated? That’s maximum without problems seen within the histogram at the some distance proper, which indicates that there is at the least triple as many points around 3.Zero as there are for any other discrete range. We also see that there’s slightly any factors above 3.Seventy five in comparison to other ranges. For the x-axis at the other-hand, things are a bit greater evened out, except for the outliers at the far proper.

Bubble Plots
With bubble plots we are capable of use numerous variables to encode statistics. The brand new one we will upload right here is length. Within the figure underneath we are plotting the quantity of french fries eaten by means of anybody vs their peak and weight. Notice that a scatter plot is only a 2d visualization tool, but that using distinctive attributes we are able to represent 3-dimensional information.

Here we are the usage of shade, position, and size. The location determines the person’s peak and weight, the shade determines the gender, and the scale determines the wide variety of french fries eaten! The bubble plot shall we us easily integrate all the attributes into one plot so that we are able to see the excessive-dimensional statistics in a simple 2d view; not anything crazy complex.

Running Your Business Efficiently in 2019

The way you select to run your commercial enterprise will have a giant impact to your achievement. The question then is, what's an appropriate manner to run a business? In case you asked several marketers this query, you’d probable get a range of various answers. The reality is that what works for one commercial enterprise may not necessarily paintings for any other and each enterprise has a extraordinary experience. With that being stated, a common feature in a a hit enterprise is a excessive stage of performance.If you hold studying underneath,you’re going to learn about efficiency and methods which you get your business to operate efficiently.

What is efficiency?
To recognize whether or not or no longer you’re going for walks your business effectively, you first need to recognize what efficiency is business Dictionary defines it as;

“The comparison of what's actually produced or executed with what may be done with the identical intake of assets.”

This definition tells you that performance is about whether or not you’re getting the maximum out of the money and time you’re spending for your commercial enterprise. Regularly, it may additionally be used as a measure of productivity too.

Enhancing productivity
Following on from the final factor, productivity is a big teller of whether or not your business is being run effectively. If it isn’t, then you need to work on tackling this area. See some suggestions beneath for how to improve productiveness.

Make employees accountable: If youwant employees to be more effective, making them responsible is vital. Delegate obligations and pressure the importance of it getting done. Additionally, within the method of doing this, offer assist in case they need it.
Praise effects: personnel want to recognise that their hard paintings is preferred, so worthwhile outcomes is critical. There's no proper manner to praise personnel so discover a selection of things likepublic recognition, bonuses, or paid day without work.
Provide the proper device:with out the proper gadget, personnel may want to without difficulty get frustrated, and performance may drop. Take a look at and preserve equipment regularly in order that it doesn’t avert them from doing their task.
Saving money
every other issue of an effectively run commercial enterprise is one that is getting the most out of what they spend. Make certain you’re getting a return on your investments and that you’re investing inside the right way. Right here are some cash saving suggestions you can attempt applying.

Recycle: Recycling should prevent a substantive sum of money, so come to be accustomed to making the most of the materials you have got. If you’ve were given large materials that may be recycled, purchase baling wire from corporations like balingwiredirect.Com in order that they may be compressed and recycled. For materials that may be reused together with workplace supplies and paper, make the high-quality use of them to reduce fees.
Lessen Overhead: To shop money and improve efficiency, lowering overhead is a tip you need to take into account. You may do that by means of making adjustments like the usage of a programmable clever thermostat, turning device off when no longer in use, and encouraging telecommuting.
Downsize: if you have offerings and system that you don’t need, do away with them or downsize so you’re the usage of what you have greater correctly. The objective ought to be to use the assets handy to get the great outcomes possible.
Whether or not or no longer you’re going for walks your enterprise successfully can be seen for your performance. If you’re meeting your objectives always, then you definately’re on the proper music and feature likely found what works quality for you.

Strategies to Improve Your Value in Your Chosen Career in 2019

At some stage in an interview, a hiring supervisor will goal to pick out how much price you could convey to a business enterprise in the event that they hired you. Simultaneously, you’ll be trying to discover how treasured the corporation could be in your profession improvement, increase, and pride.As the whole thing comes all the way down to cost at some point of the hiring process, you should enterprise to increase your very own to grow to be a proper candidate and secure your dream activity. Here are 8 techniques to enhance your cost in your preferred career.

Ask Your supervisor
rather than guessing and lacking the mark, ask your modern-day supervisor how you could offer a organization with greater fee, so you can flourish in a position. As an example, they might propose additional schooling, certifications, or that you apply yourself greater effortlessly in crew responsibilities.

Grow to be results orientated
both massive and small companies are effects-orientated. They want to reach their annual targets comfortably and paintings with personnel who can assist them do so. If you may assist a enterprise revel in steady results and exceed their preliminary expectancies, you will become one of the most treasured employees inside that enterprise. Via becoming effects orientated, you may quick climb the profession ladder, growth your annual revenue, and improve your activity pride.

Construct true Bridges
View all and sundry you meet as a ability possibility to a larger, brighter role within the organisation or someplace else. Even in case you’re in a process you view as a stepping stone, aim to build suitable bridges alongside the manner, as you never recognize the role they could play to your profession within the destiny.

Get to recognize many human beings on each a expert and private stage. As a result, your call may spring to thoughts whilst a promotional possibility arises, or they're seeking out a skilled candidate for an upcoming position at their new agency.

Earn Certifications
numerous certifications will show you have got each the choice and tough paintings ethic to no longer most effective enhance your self, however to excel in your preferred role or to enter a new enterprise. With so many on-line publications now available, which can work around your busy life, there is no excuse for now not growing your skillset and obtaining various certifications at some point of your career.

For example, in case you are wondering if you ought to turn out to be a nurse practitioner, you could embark on an internet FNP software to improve your skillset and input or grow in the healthcare industry.

Accept additional responsibility
at the same time as paintings/existence balance is critical for keeping a strain-unfastened way of life, it might be viable to take on extra duty inside your current role. Of direction, always be selective whilst accepting extra tasks to keep away from burning out and/or dropping passion for your function or a emblem.

Community
Networking couldn't handiest open a commercial enterprise up to many exciting possibilities with diverse companies or experts, however it may additionally help you at some stage in your profession. Groups of all sizes want to paintings with personnel who've many connections within their industry. The more relationships you shape at some stage in the years, the greater valuable you becomes to an employer.

To connect to human beings each inside and outside of your enterprise, you should: attain out to specialists on LinkedIn Attend diverse industry conferences, exhibitions, and so forth.
Ship a chilly email to a potential purchaser or enterprise associate prepare industry occasions/meetings enhance Your Profile
Don’t allow a enterprise to restriction your ambition. In case you want to raise your profile, look for possibilities to achieve this outside of the administrative center. As an instance, you may host seminars to percentage your know-how or enjoy. You may also apply to speak at industry meetings, or maybe write a weblog on diverse topics associated with your enjoy and/or industry.

How To Install Ubuntu Alongside Windows 10

Let’s see the steps of installing Ubuntu along side Windows 10.

Step 1: Make a backup [optional]
It is always nice to make a back up, just in case if you mess up with the system. There are numerous articles on the web to show you how to backup your system. You can follow this tutorial here.

Step 2: Create a live USB/disk of Ubuntu
The next thing you need to do is to create a live USB or disk. I recommend Universal USB Installer to create a live USB of Linux OS in Windows.

Read this screenshot tutorial to learn how to create a live USB of Ubuntu Linux in Windows.
Step 3: Make a partition where Ubuntu will be installed
Assuming tat you have a fresh system, the first thing we need to do is to make a partition to install Linux. The 256 GB in my system was already had several partitions from manufacturer but mainly for backup and other purposes. Main partition was C drive, of around 220 GB, where Windows 8.1 was installed.

Suggested read
Install Soundcloud Desktop App In Linux
If you have just one partition like this, you need to make some free space out of it for Linux. If you have several partitions of considerable size, use any of them except C drive because it may erase the data.


To make a partition in Windows 8, go to Disk Management tool. You can find disk management tool by searching for ‘disk’ in Control Panel.

disk management tool windows 8

In the Disk Management tool, right click on the drive which you want to partition and select shrink volume. In my case, I shrank the C drive to make some free space:

disk_partition_Windows8

You can leave the free space as it is. We shall use it while installing Ubuntu.

Step 4: Disable fast startup in Windows [optional]
Windows 8 introduced a new feature called “fast startup” for quick boot. While it is not mandatory, it would be better to have it disabled.


Go to Control Panel > Hardware and Sound > Power Options > System Settings > Choose what the power buttons do and uncheck the Turn on fast startup box.

If you need more hints, follow this screenshot tutorial to disable fast startup in Windows.
Step 5: Disable secureboot in Windows 10 and 8.1
This is the most important step. The new secure boot feature of Windows 8, originally intended for security feature for rootkit viruses, prevents dual booting of Windows with Linux. To dual boot Windows 8 with Linux, we must disable secure boot in UEFI.

It is better that you follow this screenshot guide to disable secure boot in Windows 10 Windows 8.1.
Step 6: Installing Ubuntu along with Windows 10, 8.1
Once you have disabled secure boot, it’s time to install Ubuntu. I hope you already created the live USB as mentioned in step 2. Plug in the USB and boot the system from it.

To boot from USB, will have to choose boot from USB option from within Windows itself. Either with PC Setting (like for UEFI) or pressing shift key while clicking on Restart.

Once you have booted in the live USB, you will be presented with option to try or install Ubuntu. Click on install. You will be presented with few screen options to choose the language. It will then do some checks on available space, power and internet connection etc. Just click on Continue.

Suggested read
How To Solve: cannot open shared object file [Quick Tip]
dual booting Ubuntu with Windows 8


The main screen which you should pay attention to is Installation Type. Choose Something else here:

Installing_Windows8_Ubuntu_1

Remember we had created some free space beforehand? We shall use the free space to create Root, Swap and Home. Select the free space and click on the + sign.

partition on Ubuntu Windows 8 dual boot

It will provide you with option to create Linux partition. We are creating the Root partition. Any thing between 10-20 GB is more than sufficient for it. Choose the size, select Ext 4 as file type and / (means root) as the mount point.

Installing_Windows8_Ubuntu_3


Clicking on OK in previous step will bring you to the partition screen. Next we will create swap. Like previously, click on the + sign again. This time use the file type as Swap area. Suggestible swap size is double of RAM.

Installing_Windows8_Ubuntu_4

In similar fashion, create a Home partition. Allocate it maximum space (in fact allocate it rest of the free space) because this is where you’ll save music, pictures and downloaded files.

Installing_Windows8_Ubuntu_5

Once you are ready with Root, Swap and Home, click on Install Now:

Installing_Windows8_Ubuntu_6


Well, you have almost won the battle. You can smell victory now. Next you will be asked to set username password etc. Basically, you just need to click next now.

Once the installation is completed, restart the computer, you should be welcomed by a purple grub screen. Enjoy Ubuntu along with Windows 10 in dual boot mode.

Note: If after installing Ubuntu, you boot directly in Windows, check in UEFI settings for changing the boot order. If you see no option to set the boot to Ubuntu, you need to fix it from within Windows. When you are in Windows desktop, hover the mouse in left corner, right click and select administrator’s command prompt. Then run the following command:

bcdedit /set "{bootmgr}" path \EFI\ubuntu\grubx64.efi
This should make the Grub default and hence you can access both Ubuntu and Windows from it.

I hope this guide helped you to dual boot Ubuntu with Windows 10 UEFI. If you want to reverse the process, follow this guide to remove Ubuntu from dual boot with Windows. Though this article is written for Ubuntu, it should be helpful for other Linux OS as well. Any questions or suggestions are always welcomed.