Kamal's version of a Python wrapper for MySQL database stuff.
To install the kamalsql
package, you can execute the below command.
pip install kamalsql
If you have already installed kamalsql
and would like to upgrade
to the latest version you could do this:
python -m pip install --upgrade kamalsql
Before getting into how to use this, here is the database info for the below examples.
-
Tables present:
department
,student
-
Table description of
department
Field Type Null Key Default Extra sname varchar(20) YES NULL regno varchar(20) YES NULL m1 int(11) YES NULL m2 int(11) YES NULL
from kamalsql import KamalSQL
db = KamalSQL(
host='localhost',
user='username',
password='mypassword',
database='mydatabase'
)
When you establish a connection in the above way, the auto-commit feature is disabled by default. If you would like to auto-commit the transactions, you can establish the connection is the following way:
from kamalsql import KamalSQL
db = KamalSQL(
host='localhost',
user='username',
password='mypassword',
database='mydatabase',
autocommit=True
)
You could also check the connection status using the status()
function:
print(db.status())
If the connection is established, you will get this:
Connection Successful
If not, this:
Connection Unsuccessful
You can get the tables present in the database using the
showTables()
function.
tables = db.showTables()
print(tables)
['department', 'student']
There are a couple of ways you could do this:
- using
describeTable()
- using
fancyDescribeTable()
The describeTable()
returns a list with the description in the form of
tuples. So, to make it more readable, one has to use a loop.
description = db.describeTable('department')
for info in description:
print(info)
('Field', 'Type', 'Null', 'Key', 'Default', 'Extra')
('sname', 'varchar(20)', 'YES', '', None, '')
('regno', 'varchar(20)', 'YES', '', None, '')
('m1', 'int(11)', 'YES', '', None, '')
('m2', 'int(11)', 'YES', '', None, '')
Here we are using the tabulate
module to pretty-print the
table description.
description = db.fancyDescribeTable('department')
print(description)
╒═════════╤═════════════╤════════╤═══════╤═══════════╤═════════╕
│ Field │ Type │ Null │ Key │ Default │ Extra │
╞═════════╪═════════════╪════════╪═══════╪═══════════╪═════════╡
│ sname │ varchar(20) │ YES │ │ │ │
├─────────┼─────────────┼────────┼───────┼───────────┼─────────┤
│ regno │ varchar(20) │ YES │ │ │ │
├─────────┼─────────────┼────────┼───────┼───────────┼─────────┤
│ m1 │ int(11) │ YES │ │ │ │
├─────────┼─────────────┼────────┼───────┼───────────┼─────────┤
│ m2 │ int(11) │ YES │ │ │ │
╘═════════╧═════════════╧════════╧═══════╧═══════════╧═════════╛
Now that we know how to establish connection, lets see how to insert values into a table.
db.insert(
'department',
{
'sname': 'Akash',
'regno': '391201',
'm1': 95,
'm2': 96
}
)
db.insert(
'department',
{
'sname': 'Anish',
'regno': '391202',
'm1': 92,
'm2': 91
}
)
db.insert(
'department',
{
'sname': 'Bala',
'regno': '391203',
'm1': 91,
'm2': 90
}
)
db.insert(
'department',
{
'sname': 'Dinesh',
'regno': '391204',
'm1': 65,
'm2': 86
}
)
If you have not enabled the auto-commit feature, then make sure to use
db.commit()
to complete the transaction.
- We saw how to insert values.
- Now lets check if they have been inserted into the table.
- This can be done by invoking the
getAll()
function. getAll()
returns a list with dictionary objects.
records = db.getAll('department')
for record in records:
print(record)
{'sname': 'Akash', 'regno': '391201', 'm1': 95, 'm2': 96}
{'sname': 'Anish', 'regno': '391202', 'm1': 92, 'm2': 91}
{'sname': 'Bala', 'regno': '391203', 'm1': 91, 'm2': 90}
{'sname': 'Dinesh', 'regno': '391204', 'm1': 65, 'm2': 86}
- By default, if you pass just the table name, it will select all the columns.
- If you wish to view the above output in a tabular format, then you
could use
tableFormatRows()
to do the same.
rows = db.getAll('department')
table = db.tableFormatRows(rows)
print(table)
sname regno m1 m2
------- ------- ---- ----
Akash 391201 95 96
Anish 391202 92 91
Bala 391203 91 90
Dinesh 391204 65 86
-
See, I know that the above statements can be combined to form a one-liner like this:
print(db.tableFormatRows(db.getAll('department')))
but, for the sake of simplicity and to make things more clearer, I am doing it in that way. -
If you would like to add a few more conditions, then you could invoke the
getAll()
function in this way:
rows = db.getAll(
'department', # Table to be selected
['regno', 'sname'], # Columns to display
("regno >= %s and regno <= %s", # WHERE clause
['391201', '391204'] # values for the arguments used in WHERE
),
["regno", "asc"], # ORDER BY regno asc
[0, 2] # LIMIT 0, 2
)
for row in rows:
print(row)
print('\nTabular Format: \n')
print(db.tableFormatRows(rows))
{'regno': '391201', 'sname': 'Akash'}
{'regno': '391202', 'sname': 'Anish'}
Tabular Format:
regno sname
------- -------
391201 Akash
391202 Anish
- We saw how to insert and the various ways to see the table's content.
- Now, lets see how to update existing values.
- Note: Don't forget to give the
WHERE
condition. If you don't provide that, the update condition will get applied to the entire table.
db.update(
'department', # The table
{ # The new values
"sname": "Frank",
"regno": "391205"
},
("regno = %s", ['391204']) # the WHERE condition.
)
rows = db.getAll('department')
print(db.tableFormatRows(rows))
sname regno m1 m2
------- ------- ---- ----
Akash 391201 95 96
Anish 391202 92 91
Bala 391203 91 90
Frank 391205 65 86
- We saw how to insert, view and the various ways to see the table's content.
- Now, lets see how to remove record(s).
- Note: Don't forget to give the
WHERE
condition. If you don't provide that, theDELETE
condition will get applied to the entire table.
db.delete(
'department', # Table involved
("regno = %s", ['391205']) # the WHERE clause
)
rows = db.getAll('department')
print(db.tableFormatRows(rows))
sname regno m1 m2
------- ------- ---- ----
Akash 391201 95 96
Anish 391202 92 91
Bala 391203 91 90