Now next we discuss how can we work with the database in python and how to connect the database, how to create, and much more. So let's get started. First, discuss the database, what is it, why used, and different types of databases.
Database, also called electronic database, any collection of data, or information, that is specially organized for rapid search and retrieval by a computer. Databases are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations. A database management system (DBMS) extracts information from the database in response to queries.
There are various types of DataBases available as per the application. Some of them are listed here:
Distributed databases:
A distributed database is a type of database that has contributions from the common database and information captured by local computers. In this type of database system, the data is not in one place and is distributed at various organizations.
Relational databases:
This type of database defines database relationships in the form of tables. It is also called Relational DBMS, which is the most popular DBMS type in the market. Database example of the RDBMS system include MySQL, Oracle, and Microsoft SQL Server database.
Object-oriented databases:
This type of computers database supports the storage of all data types. The data is stored in the form of objects. The objects to be held in the database have attributes and methods that define what to do with the data. PostgreSQL is an example of an object-oriented relational DBMS.
Centralized database:
It is a centralized location, and users from different backgrounds can access this data. This type of computers databases store application procedures that help users access the data even from a remote location.
Open-source databases:
This kind of database stored information related to operations. It is mainly used in the field of marketing, employee relations, customer service, of databases.
Cloud databases:
A cloud database is a database which is optimized or built for such a virtualized environment. There are so many advantages of a cloud database, some of which can pay for storage capacity and bandwidth. It also offers scalability on-demand, along with high availability.
Data warehouses:
Data Warehouse is to facilitate a single version of truth for a company for decision making and forecasting. A Data warehouse is an information system that contains historical and commutative data from single or multiple sources. Data Warehouse concept simplifies the reporting and analysis process of the organization.
NoSQL databases:
NoSQL database is used for large sets of distributed data. There are a few big data performance problems that are effectively handled by relational databases. This type of computers database is very efficient in analyzing large-size unstructured data.
Graph databases:
A graph-oriented database uses graph theory to store, map, and query relationships. These kinds of computers databases are mostly used for analyzing interconnections. For example, an organization can use a graph database to mine data about customers from social media.
OLTP databases:
OLTP another database type which able to perform fast query processing and maintaining data integrity in multi-access environments.
Personal database:
A personal database is used to store data stored on personal computers that are smaller and easily manageable. The data is mostly used by the same department of the company and is accessed by a small group of people.
Multimodal database:
The multimodal database is a type of data processing platform that supports multiple data models that define how the certain knowledge and information in a database should be organized and arranged.
Document/JSON database:
In a document-oriented database, the data is kept in document collections, usually using the XML, JSON, BSON formats. One record can store as much data as you want, in any data type (or types) you prefer.
Hierarchical:
This type of DBMS employs the "parent-child" relationship of storing data. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is a hierarchical database example.
Network DBMS:
This type of DBMS supports many-to-many relations. It usually results in complex database structures. RDM Server is an example of database management system that implements the network model.
For working in python there are many APIs available from which we can connect with different types of DBs.Some of Them are:
- IBM DB2
- FireBird
- Informix
- Ingres
- MySQL
- Oracle
- PostgreSQL
- Microsoft SQL Server
- Microsoft Access
- Sybase
There are many more DataBase Connections available in python which are listed on python official page: DatabaseInterface.
Most Python's database interface remains to Python's DB-API standard, and most of the databases have ODBC support. Other than that, the Java database usually supports JDBC, and programmers can work with that from Jython.
Using the Python structure, DB-API provides standard and support for working with databases. The API consists of:
- Bring in the API module
- Obtain database connection
- Issue SQL statements and then store procedures
- Close the connection
Let's check How to we work with Python DataBase API.
MySQL with Python
To access the MySQL DB using python first we need to install and then by using the below code we can create a MySQL DataBase using python.
# importing the module
import MySQLdb
# opening a database connection
db = MySQLdb.connect ("localhost","testprog","stud","PYDB")
# define a cursor object
cursor = conn.cursor
# drop table if exists
Cursor.execute("IF STUDENT TABLE EXISTS DROP IT")
# query
sql = "CREATE TABLE STUDENT (NAME CHAR(30) NOT NULL, CLASS CHAR(5), AGE INT, GENDER CHAR(8), MARKS INT"
# execute query
cursor.execute(sql)
# close object
cursor.close()
# close connection
conn.close()
As we see above we close the connection and cursor object as we close in the file object. In other languages, developers are forced to use try...except...finally every time they work with a file (or any other type of resource that needs to be closed, like sockets or database connections). Luckily, Python loves us and gives us a simple way to make sure all resources we use are properly cleaned up, regardless of if the code returns or an exception is thrown: context managers.
There is also one more way to create a database using with statement which closes database itself. Let's see the benefit of Context Management.
- ensuring that the resources are released even if we encounter some unhandled exceptions
- readability
- convenience — we make it easier for ourselves, as we will no longer forget to close the connections to external resources.
“Typical uses of context managers include saving and restoring various kinds of global state, locking and unlocking resources, closing opened files, etc.”
Thanks for reading.
Top comments (0)