How To Make A Database In Python

Python Database Access — Python 3 MySQL

Python Database Access — Python three MySQL

Python Database Access

A database is a drove of tables related to each other via columns. For most real-world projects, a database is a must. Nosotros can use SQL (Structured Query Language) to create, access, and manipulate data. We can likewise make use of normalization to avert redundancy of data.
Do yous know How Python Copy a File — 9 Simple & Quick Means
For database programming, Python supports many database servers-
MySQL, Oracle, PostgreSQL, SQLite, Sybase, Microsoft SQL Server, mSQL, Microsoft Access, and many more than. Information technology also supports Data Query Statements, Information Definition Language (DDL), and Data Manipulation Language (DML). The standard database interface for Python is Python DB-API. For that, we have the module MySQLdb for MySQL. This is contained of database engines; so we can write Python scripts to admission any database engine. Notwithstanding, this isn't compatible with Python 3.
So, in this Python Database Access tutorial, we use the module PyMySQL.

Advantages of Database Programming with Python

With Python, nosotros have the following benefits:

• Platform-independent
• Faster and more efficient
• Portable
• Support for relational database systems
• Easy to drift and port database application interfaces
• Support for SQL cursors
• It handles open and closed connections

PyMySQL and Installation

PyMySQL implements the Python Database API two.0. In this Python Database tutorial, we will use it to connect to a MySQL database server from Python. We have the following requirements to install PyMySQL-
a. Python (any of)

• CPython>=two.6 or >=iii.3
• PyPy>=iv.0
• IronPython ii.7

Follow this link to know How Python Rename File — Single & Multiple Files
b. MySQL(any of)

• MySQL>=4.1
• MariaDB>=5.1

To install information technology, run the following control in the command prompt-

1. C:\Users\lifei>pip install PyMySQL
2. Collecting PyMySQL

Using cached https://files.pythonhosted.org/packages/2f/be/4310bb405eb83b615cf9bd4501942d9ff000d8b9372ce84e920facbf5c36/PyMySQL-0.9.0-py2.py3-none-whatever.whl
Collecting cryptography (from PyMySQL)
Downloading https://files.pythonhosted.org/packages/67/62/67faef32908026e816a74b4b97491f8b9ff393d2951820573599c105cc32/cryptography-2.ii.two-cp36-cp36m-win_amd64.whl (ane.3MB)
100% |████████████████████████████████| 1.3MB 596kB/southward
Collecting idna>=2.1 (from cryptography->PyMySQL)
Downloading https://files.pythonhosted.org/packages/4b/2a/0276479a4b3caeb8a8c1af2f8e4355746a97fab05a372e4a2c6a6b876165/idna-2.7-py2.py3-none-any.whl (58kB)
100% |████████████████████████████████| 61kB 1.3MB/s
Collecting asn1crypto>=0.21.0 (from cryptography->PyMySQL)
Using cached https://files.pythonhosted.org/packages/ea/cd/35485615f45f30a510576f1a56d1e0a7ad7bd8ab5ed7cdc600ef7cd06222/asn1crypto-0.24.0-py2.py3-none-any.whl
Collecting half-dozen>=1.4.i (from cryptography->PyMySQL)
Let'southward Read Python Zipfile — Benefits, Modules, Objects in Zipfiles in Python
Using cached https://files.pythonhosted.org/packages/67/4b/141a581104b1f6397bfa78ac9d43d8ad29a7ca43ea90a2d863fe3056e86a/six-1.11.0-py2.py3-none-whatsoever.whl
Collecting cffi>=1.7; platform_python_implementation != "PyPy" (from cryptography->PyMySQL)
Downloading https://files.pythonhosted.org/packages/2f/85/a9184548ad4261916d08a50d9e272bf6f93c54f3735878fbfc9335efd94b/cffi-1.eleven.5-cp36-cp36m-win_amd64.whl (166kB)
100% |████████████████████████████████| 174kB 568kB/due south
Collecting pycparser (from cffi>=one.7; platform_python_implementation != "PyPy"->cryptography->PyMySQL)
Using cached https://files.pythonhosted.org/packages/8c/2d/aad7f16146f4197a11f8e91fb81df177adcc2073d36a17b1491fd09df6ed/pycparser-2.xviii.tar.gz
Installing collected packages: idna, asn1crypto, six, pycparser, cffi, cryptography, PyMySQL
Running setup.py install for pycparser … done
Successfully installed PyMySQL-0.9.0 asn1crypto-0.24.0 cffi-1.11.five cryptography-ii.two.2 idna-2.seven pycparser-ii.eighteen half dozen-1.11.0

Besides, make sure to install a database server on your car. In this article, we use MySQL. We download it from here-
dev.mysql.com/downloads/mysql

Connecting Python Database

Now that you've installed everything, permit's begin connecting to the database. Let'due south create a database commencement.

a. How to Create Python Database?

mysql> create database demo;
Query OK, one row affected (0.21 sec)
mysql> use demo;
Database changed
mysql> create user 'ayushi'@'localhost' IDENTIFIED BY 'yourpassword'
-> ;
Query OK, 0 rows affected (0.21 sec)
mysql> grant all on demo.* to 'ayushi'@'localhost';
Query OK, 0 rows affected (0.22 sec)
mysql> create tabular array educatee(fname varchar(xx), lname varchar(twenty), age int, enrolment_no varchar(12));
Query OK, 0 rows affected (0.62 sec)

b. How to Connect Python Database?

1. >>> import pymysql
2. >>> db=pymysql.connect("localhost","ayushi","yourpassword","demo") #This saves a connection object into db
3. >>> cursor=db.cursor()
4. >>> cursor.execute("SELECT VERSION()")

1

1. >>> print(f"You're running version {cursor.fetchone()}")

You're running version ('8.0.11',)

1. >>> db.close() #Closing the database connection

A cursor is an object that submits different SQL statements to the database server. A cursor returns a issue set object.

Let'southward Know Python Tools — 4 Major Utilities of Python

How to Create Tables in Python Database?

At present let's have a await at all operations i by one, starting with creating a table.

1. >>> import pymysql
2. >>> db=pymysql.connect("localhost","ayushi","yourpassword","demo")
3. caching sha2: succeeded by fast path.
4. >>> cursor=db.cursor()
5. >>> cursor.execute("DROP TABLE IF EXISTS educatee") #This drops the table and replaces it
6. >>> query="""CREATE TABLE student(
7. fname VARCHAR(20), lname VARCHAR(20),
8. historic period INT, enrolment_no VARCHAR(12))"""
9. >>> cursor.execute(query)
10. >>> db.close()

How to Insert a Tape in Python Database?

Permit's try inserting a record in 'student'.

1. >>> import pymysql
2. >>> db=pymysql.connect("localhost","ayushi","yourpassword","demo")
3. caching sha2: succeeded by fast path.
4. >>> cursor=db.cursor()
5. >>> query='INSERT INTO pupil VALUES("Ayushi","Sharma",22,"0812CS141028")'
6. >>> endeavour:
7. cursor.execute(query)
8. db.commit() #Commit writing to the database
9. except:
10. db.rollback() #Rollback the transaction if not consummate
11. 1
12. >>> db.close()

Let'due south bank check if this makes whatsoever changes to the database. In the command prompt:

1. mysql> select * from student;
2. + — — — — + — — — — + — — — + — — — — — — — +
3. | fname | lname | historic period | enrolment_no |
4. + — — — — + — — — — + — — — + — — — — — — — +
5. | Ayushi | Sharma | 22 | 0812CS141028 |
6. + — — — — + — — — — + — — — + — — — — — — — +
7. 1 row in set (0.00 sec)

How to Read Records in Python Database?

At present how can we fetch values from a database? Let'southward take an example to fetch records of students from 'student' that are older than 22. We take added another tape for this purpose.

1. >>> import pymysql
2. >>> db=pymysql.connect("localhost","ayushi","yourpassword","demo")
3. caching sha2: succeeded by fast path.
4. >>> cursor=db.cursor()
5. >>> query="select * from student where age>22"
6. >>> attempt:
7. cursor.execute(query)
8. resultset=cursor.fetchall() #To fetch all records that satisfy
9. for record in resultset:
10. fname=record[0]
11. lname=record[1]
12. age=tape[2]
13. enrolment_no=record[three]
14. print(f"Pupil: {fname} {lname}; Enrolment: {enrolment_no}; Historic period: {age}")
15. except:
16. print("Distressing, we encountered a problem")
17. one
18. Student: Megha Sharma; Enrolment: 0812CS141015; Age: 24
19. >>> db.shut()

We take the following methods and attributes-

• fetchone()– This fetches the immediate side by side row from the result set of the query.
• fetchall()– This fetches the unabridged event set; information technology will exclude the records already extracted.
• rowcount– This is an attribute. It returns an integer denoting the number of records that a call to execute() affected.

Take a look at XML Processing in Python 3 | XML Parser

How to Update Records in Python Database?

To update an existing record, nosotros can simply utilize an SQL query for the same.

1. >>> import pymysql
2. >>> db=pymysql.connect("localhost","ayushi","yourpassword","demo")
3. caching sha2: succeeded past fast path.
4. >>> cursor=db.cursor()
5. >>> query="update student set age=age+1 where age<=22"
6. >>> endeavour:
7. cursor.execute(query)
8. db.commit()
9. except:
10. db.rollback()
11. 1
12. >>> db.close()

Let's come across if this has made any changes to the actual database. In your control prompt:

1. mysql> select * from educatee;
2. + — — — — + — — — — + — — — + — — — — — — — +
3. | fname | lname | age | enrolment_no |
4. + — — — — + — — — — + — — — + — — — — — — — +
5. | Ayushi | Sharma | 23 | 0812CS141028 |
6. | Megha | Sharma | 24 | 0812CS141015 |
7. + — — — — + — — — — + — — — + — — — — — — — +
8. 2 rows in prepare (0.00 sec)

How to Delete Records in Python Database?

We can also delete records from a database using Python.

1. >>> import pymysql
2. >>> db=pymysql.connect("localhost","ayushi","swaysway7!","demo")
3. caching sha2: succeeded by fast path.
4. >>> cursor=db.cursor()
5. >>> query="delete from student where age>23"
6. >>> try:
7. cursor.execute(query)
8. db.commit()
9. except:
10. db.rollback()
11. 1
12. >>> db.close()

And in the control prompt:

1. mysql> select * from student;
2. + — — — — + — — — — + — — — + — — — — — — — +
3. | fname | lname | age | enrolment_no |
4. + — — — — + — — — — + — — — + — — — — — — — +
5. | Ayushi | Sharma | 23 | 0812CS141028 |
6. + — — — — + — — — — + — — — + — — — — — — — +
7. 1 row in prepare (0.00 sec)

Commit, Rollback, and Disconnecting

A commit command tells the database to finalize the write to the database. A rollback lets us revert changes and become back to a previous country. For committing, you can employ commit(), and for rollback, you tin apply rollback().
Permit'due south hash out Python Network Programming | Python Socket Programming
After nosotros're washed working with the database, nosotros should close the database to release resources. We utilise close() for this. If you don't become whatsoever of this, we suggest reading up on the basic properties of transactions in databases.

Errors in Transactions

When holding a transaction, you may come up across ten different kinds of errors:

Python Database Admission — Python 3 MySQL

a. Error

This is the base of operations form for errors and a bracket to StandardError.

b. InterfaceError

This is a subclass to Error and Python uses it for errors relating to the module for database admission.

c. DatabaseError

This is a subclass to Error and Python uses information technology for database errors.

d. OperationalError

This is a subclass of DatabaseError. When Python loses connection to a database, it throws this error.
This may happen when we haven't selected a database.

Permit's Explore Python Spider web Framework — A Detailed List with Explanations

east. DataError

This is a subclass of DatabaseError. Python uses this when in that location is an error in the data.

f. InternalError

This is a bracket of DatabaseError. Python uses this for errors internal to the module we apply for the database access.

one thousand. IntegrityError

Too a subclass of DatabaseError. Python uses this for cases where there tin be damage to relational integrity.
This may happen when you effort to enter duplicate records in the database.

h. ProgrammingError

This is a bracket of DatabaseError. Errors like bad table names cause this.
This may happen when we try to create a duplicate database.

i. NotSupportedError

A bracket of DatabaseError. When we attempt to call functionality that information technology doesn't support, Python raises this error.

j. Warning

This is a bracket of StandardError. Python uses this for not-fatal issues.
So, this was all nearly Python Database Access. Hope you lot like our caption.

Decision

Hence, now y'all know how to admission a database using Python with MySQL. In addition, we saw how to perform Python database access and how to create a database in Python 3 and also perform operations like insert, read, update, delete, commit, rollback, and disconnect. At last, nosotros cover how to bargain with errors in Python Database Admission and PyMySQL and Installation and the benefits of Python database admission. That'due south all for today. Drop whatsoever queries in the comments below.

Source: https://medium.com/@rinu.gour123/python-database-access-python-3-mysql-e0ad5164854

Posted by: smithsualind.blogspot.com

Share this post