Repository navigation
Expand file tree
/
Copy path032-python-mysql.py
More file actions
105 lines (73 loc) · 3.31 KB
/
Copy path032-python-mysql.py
File metadata and controls
105 lines (73 loc) · 3.31 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
""" MARKDOWN
---
Title: python mysq; database
MetaDescription: python Connect to mysql database, CRUD, insert, update, select, mysql, create example code, tutorials
Author: Venkata Bhattaram / tinitiate.com
ContentName: python-mysql-database
---
MARKDOWN """
""" MARKDOWN
# Python pymysql mysql Database Programming
* PYTHON provides mechanisms to connect to various databases
and perform database operations like Create, insert, select
* There are many Open and Commercial Database connectivity
modules available for download.
* In this program, we test with pymysql an OpenSource PYTHON database
connectivity module, And the pymysql module is used to connect to mysql Database
* Almost all popular databases have Modules to connect to python.
MARKDOWN """
# MARKDOWN ```
import pymysql
# Create Connection Object
# ##############################################################################
conn = pymysql.connect(host='localhost', port=3306, user='my_user_name', passwd='my_password', db='mysql')
cursor = conn.cursor()
# Executing DDL (Data Definition Language)
# ##############################################################################
# Try to Drop the table
try:
cursor.execute("drop table ti_test");
except:
pass;
# Python pymysql MYSQL Create a table with multiple data types
cursor.execute(" create table ti_test ( col1 int, col2 varchar(100), col3 date, col4 text );" )
# python pymysql MYSQL Alter Table to add primary key
cursor.execute("alter table ti_test add constraint ti_test_pk primary key (col1);")
# Executing DML (Data Manipulation Language)
# ##############################################################################
# Python pymysql MYSQL Insert data into the table
cursor.execute("insert into ti_test values (1, 'Test', curdate(), 'THIS IS CLOB DATA .........');")
cursor.execute("insert into ti_test values (2, 'Test2', curdate(), 'THIS IS CLOB DATA .........');")
# python pymysql MYSQL Update data in the table
cursor.execute("update ti_test set col2 = 'DATA CHANGE' where col1 = 1;")
# python pymysql MYSQL delete data in the table
cursor.execute("delete from ti_test where col1 = 2;")
cursor.execute("insert into ti_test values (3, 'Test3', curdate(), '333 THIS IS CLOB DATA .........');")
cursor.execute("insert into ti_test values (4, 'Test4', curdate(), '444 THIS IS CLOB DATA .........');")
# Commit changes
conn.commit()
cursor.execute("insert into ti_test values (5, 'Test5', curdate(), '555 THIS IS CLOB DATA .........');")
# To RollBack changes
conn.rollback()
# Executing Data Selection
# ##############################################################################
# Fetch One Row
cursor.execute("select col1,col2,col3,col4 from ti_test;")
# Prints the Row count of the cursor
print(cursor.rowcount)
# Fetches a Single Row
rowdata = cursor.fetchone()
print(rowdata)
# Reading multiple datatypes and print to screen
cursor.execute("select col1,col2,col3,col4 from ti_test;")
# use fetchall() to get the list of row data
for l_col1, l_col2, l_col3, l_col4 in cursor.fetchall():
print("l_col1: ", l_col1)
print("l_col2: ", l_col2)
print("l_col3: ", l_col3)
print("l_col4: ", l_col4)
# Close the Cursor
cursor.close()
# Close the Connection
conn.close()
# MARKDOWN ```