、Python MySQL
-----------------------------------------------------
1. MySQL
2. mysql python
a. PyMySQL-master.zip
https://github.com/PyMySQL/PyMySQL
3. ， ， cmd，
cmd> python setup.py install
4. idea
5. python ，
import pymysql
print("================ ====================")
try:
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
cur = conn.cursor()
cur.execute('select version()')
version = cur.fetchone()
print(version)
cur.close()
conn.close()
except Exception:
print(" ")
================ ====================
('5.5.60',)
、 Python SQL
--------------------------------------------------------------------------
1.create and Drop Table
# print("================ Drop Table====================")
# try:
# conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
# cur = conn.cursor()
# cur.execute('drop table t1')
# cur.close()
# conn.close()
# except Exception:
# print(" ")
# print("================ Create Table====================")
# try:
# conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
# cur = conn.cursor()
# cur.execute('create table t1(id int primary key auto_increment, name varchar (20), age int)')
# conn.commit()
# cur.close()
# conn.close()
# except Exception:
# print(" ")
2.insert into
--------------------------------------------------------------------
print("================ Insert ====================")
try:
#
conn = pymysql.connect(host = "localhost", user = "mysql", passwd= "mysql",db = "python",port = 3306,charset="utf8");
#
cur = conn.cursor();
#
sql = "insert into t1 (name,age) values ('%s','%d')" %('tom',12);
cur.execute(sql);
conn.commit();
cur.close()
conn.close()
except Exception:
print(" ")
----------------------------------------------------------------------------------------
print("================ Insert 10000 ====================")
print("================ ====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
# sql
i = 0 ;
while i < 10000 :
sql = "insert into t1(name,age) values('%s',%d)" % ("tom" + str(i),i % 100);
#
if(i == 50):
sql = "insert"
# sql
cur.execute(sql)
i += 1 ;
#
conn.commit()
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()
3.delect from
print("================ Delete====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
# sql
sql = "delete from t1 where id > 50";
cur.execute(sql)
#
conn.commit()
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()
4.update set
print("================ Update ====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
# sql
sql = "update t1 set age = age - 20 where age > 20";
cur.execute(sql)
#
conn.commit()
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()
5.select from
print("================ select from ====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
# sql
sql = "select * from t1";
cur.execute(sql)
res = cur.fetchall()
for r in res :
print(str(r[0]) + "," + r[1] + "," + str(r[2]));
#
conn.commit()
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()
6.select count(*)
print("================ select count(*) from ====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
# sql
sql = "select count(*) from t1 where id > 10";
cur.execute(sql)
res = cur.fetchone()
print(res[0])
#
conn.commit()
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()
7. Mysql
print("================ Mysql ====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
# sql
sql = "call mydata.up_add(1,2,@q);";
cur.execute(sql)
cur.execute("select @q");
res = cur.fetchone()
print(res[0])
conn.commit()
#
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()
8. Mysql
print("================ Mysql ====================")
try:
#
conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='mydata', port=3306, charset='utf8')
#
cur = conn.cursor()
#
conn.autocommit(False);
#
sql = "create table mytable(id int primary key auto_increment, name varchar(20), password varchar(20), age int)";
cur.execute(sql)
#
cur.execute("call big_insert2(100)");
#
sql = "select * from mytable";
cur.execute(sql)
res = cur.fetchall()
for r in res:
print(str(r[0]) + "," + str(r[1]) + "," +str(r[2]) + "," +str(r[3]))
conn.commit()
#
except Exception:
print(" , ")
conn.rollback()
finally:
#
cur.close()
conn.close()