MySQL Python operation

configuration

win_64 Ubuntu14.04 Python3.x

pip installs the pymysql module

Install pip install pymysql directly using pip

win64 is executed directly in cmd

Connect to a local database

Connect to the database using the module pymysql

#!/usr/bin/python
# coding=utf-8
import pymysql
#  Connect to a local database
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='a123', db='samp_db1', charset='utf8')
cursor = conn.cursor()
cursor.execute('select * from bigstu')
for row in cursor.fetchall():
  print(row)
#  check
cursor.execute('select id, name from bigstu where age > 22')
for res in cursor.fetchall():
  print(str(res[0]) + ", " + res[1])
cursor.close()
print('-- end --')

Output:

(1, ' zhang 3', ' male ', 24, datetime.date(2017, 3, 29), '13666665555')
(6, ' pika~chu ', ' male ', 23, datetime.date(2017, 3, 11), '778899888')
(8, ' pikachu~pi ', ' female ', 20, datetime.date(2017, 3, 13), '13712345678')
(12, ' pikapi ', ' male ', 21, datetime.date(2017, 3, 7), '13787654321')
1,  zhang 3
6,  pika~chu
-- end --

You can directly execute the sql statement. The result is a tuple.

increase

Insert data

Insert 1 piece of data and the code that follows

insertSql = "insert into bigstu (name, sex, age, mobile) values ('%s','%s',%d,'%s') "
xiuji = (' Show, ', ' male ', 15, '13400001111')
cursor.execute(insertSql % xiuji)
conn.commit() #  Don't forget to submit

Add columns

Add a column cash after mobile

addCo = "alter table bigstu add cash int after mobile"
cursor.execute(addCo)

If you want to set the default value

addCo = "alter table bigstu add cash int default 0 after mobile"
cursor.execute(addCo)

delete

Delete the data

Delete the data of name = soji

deleteSql = "delete from bigstu where name = '%s'"
cursor.execute(deleteSql % ' Show, ')

Delete the column

Delete cash column

dropCo = "alter table bigstu drop cash"
cursor.execute(dropCo)

change

Modify the data

Update qualified data

updateSql = "update bigstu set sex = '%s' where name = '%s'"
updateXiuji = (' Show, ', ' Show, ') #  The gender of hideki is hideki
cursor.execute(updateSql % updateXiuji)
conn.commit()

To deal with things

Add cash to a record

table = "bigstu"
addCash = "update " + table + " set cash = cash + '%d' where name = '%s'"
lucky = (1000, " Show, ")
try:
  cursor.execute(addCash % lucky)
except Exception as e:
  conn.rollback()
  print(" The increase failed ")
else:
  conn.commit()

Directly execute SQL statement, 10 points convenient

Code snippet

Add columns to the database

Read the column names to be added from json and get all the column names in the current two tables

Collate the column names that need to be inserted, and then insert the column into the corresponding table

import pymysql
import json
import os
import secureUtils
mapping_keys = json.load(open("key_mapping_db.json", "r"))
db_keys = [] # json All of the key
for k in mapping_keys.values():
  db_keys.append(k)
conn = pymysql.connect(host='localhost', port=3306, user='root',
            passwd='*****', db='db_name', charset='utf8')
cursor = conn.cursor()
table_main = "table_main"
main_table_keys = [] #  The column name of the main table
cursor.execute("show columns from " + table_main)
for row in cursor.fetchall():
  main_table_keys.append(row[0])
staff_table_keys = []
cursor.execute("show columns from table_second")
for row in cursor.fetchall():
  staff_table_keys.append(row[0])
need_to_insert_keys = []
for k in db_keys:
  if k not in staff_table_keys and k not in main_table_keys and k not in need_to_insert_keys:
    need_to_insert_keys.append(k)
print("need to insert " + str(len(need_to_insert_keys)))
print(need_to_insert_keys)
for kn in need_to_insert_keys:
  print("add key to db " + kn)
  cursor.execute("alter table staff_table add " + kn +" text")
conn.close()

Change the field character

Change all the fields in main_table_keys to utf8

(1, ' zhang 3', ' male ', 24, datetime.date(2017, 3, 29), '13666665555')
(6, ' pika~chu ', ' male ', 23, datetime.date(2017, 3, 11), '778899888')
(8, ' pikachu~pi ', ' female ', 20, datetime.date(2017, 3, 13), '13712345678')
(12, ' pikapi ', ' male ', 21, datetime.date(2017, 3, 7), '13787654321')
1,  zhang 3
6,  pika~chu
-- end --

0