#-*-coding:utf-8-*-
import pymysql
class get_Mysql(object):
def __init__(self,dbname,company,goods_name):
self.dbname = dbname
self.table_name = '{}_{}'.format(company,goods_name)
self.conn = pymysql.connect(
host = '127.0.0.1',
user = 'root',
password = '123456',
port = 3306,
db = self.dbname,
charset = 'utf8'
)
self.cursor = self.conn.cursor()
def create_table(self):
create_sql = ''' CREATE TABLE '{tbname}' (
{id} INT (15) PRIMARY KEY,
{price} VARCHAR (15) NOT NULL,
{is_jd} CHAR (30) NOT NULL DEFAULT NULL,
{shopname} VARCHAR (30) NOT NULL DEFAULT NULL,
{brand} VARCHAR (20) NOT NULL DEFAULT NULL,
{years} VARCHAR (10) NOT NULL DEFAULT NULL,
{months} VARCHAR (10) NOT NULL DEFAULT NULL,
{weight} VARCHAR (10) NOT NULL DEFAULT NULL,
{thick} VARCHAR (40) NOT NULL DEFAULT NULL,
{long} VARCHAR (40) NOT NULL DEFAULT NULL,
{cpu_brand} VARCHAR (30) NOT NULL DEFAULT NULL,
{cpu_num} VARCHAR (20) NOT NULL DEFAULT NULL,
{sim_num} VARCHAR (25) NOT NULL DEFAULT NULL,
{sim} VARCHAR (20) NOT NULL DEFAULT NULL,
{rom} VARCHAR (10) NOT NULL DEFAULT NULL,
{ram} VARCHAR (15) NOT NULL DEFAULT NULL,
{sizes} VARCHAR (20) NOT NULL DEFAULT NULL,
{front_c} VARCHAR (20) NOT NULL DEFAULT NULL,
{back_c} VARCHAR (20) NOT NULL DEFAULT NULL,
{battery} VARCHAR (45) NOT NULL DEFAULT NULL,
{total_com} INT (20) NOT NULL DEFAULT 0,
{good_com} INT (20) NOT NULL DEFAULT 0,
{mid_com} INT (20) NOT NULL DEFAULT 0,
{bad_com} INT (20) NOT NULL DEFAULT 0,
{good_lv} FLOAT (20),
{mid_lv} FLOAT (20),
{bad_lv} FLOAT (20)
)
'''
try:
self.cursor.execute(create_sql.format(tbname=self.table_name,id='id',price='price',is_jd='is_jd',shopname='shopname',brand='brand',
years='years',months='months',weight='weight',thick='thick',long='long',cpu_brand='cpu_brand',
cpu_num='cpu_num',sim_num='sim_num',sim='sim',rom='rom',ram='ram',sizes='sizes',
front_c='front_c',back_c='back_c',battery='battery',total_com='total_com',good_com='good_com',
mid_com='mid_com',bad_com='bad_com',good_lv='good_lv',mid_lv='mid_lv',bad_lv='bad_lv'))
except Exception as e:
self.conn.rollback()
print('Create table failure, cause:',e)
else:
self.conn.commit()
print('The table is successful and the name is{}'.format(self.table_name))
def insert(self,data):
insert_sql = '''INSERT INTO '{tbname}'(id,price,is_jd,shopname,brand,years,months,weight,thick,long,cpu_brand,cpu_num,sim_num,sim,
rom,ram,sizes,front_c,back_c,battery,total_com,good_com.mid_com,bad_com,good_lv,mid_lv,bad_lv)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
try:
self.cursor.execute(insert_sql.format(self.table_name),data['id'],data['price'],data['is_jd'],data['shopname'],data['brand'],data['years'],data['months'],data['weight'],data['thick'],
data['long'],data['cpu_brand'],data['cpu_num'],data['sim_num'],data['sim'],data['rom'],data['ram'],data['sizes'],data['front_c'],
data['back_c'],data['battery'],data['total_com'],data['good_com'],data['mid_com'],data['bad_com'],data['good_lv'],data['mid_lv'],data['bad_lv'])
except Exception as e:
self.conn.rollback()
print("Insert data failure, cause：",e)
else:
self.conn.commit()
print('Insert a data successfully!')
def close_table(self):
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
data ={'id':13108530411,'price':'2900.00','is_jd':'self-operation','shopname':'Chili mobile phone flagship store',"brand":'Hot pepper','years':'2017','months':'June','weight':'164（Contain the battery）',"thick":"8.9（Note: subject to the product configuration and manufacturing process, the actual size of the body is different. Please refer to the physical object）",
'long':'145（Note: subject to the product configuration and manufacturing process, the actual size of the body is different. Please refer to the physical object）','cpu_brand':'Snapdragon','cpu_num':'Four nuclear','sim_num':'Double card double for single pass','sim':'Nano SIM','rom':'32GB','ram':'4GB',"sizes":'5.0 inches','front_c':'8 million pixels','back_c':'16 million pixels','battery':'4000mAh (Typical capacity)/3900mAh (Nominal capacity','total_com':1400,
'good_com':1300,'mid_com':60,'bad_com':40,'good_lv':0.925,'mid_lv':0.043,'bad_lv':0.032}
my = get_Mysql('e-commerce','JD','phone')
my.create_table()
my.insert(data)
my.close_table()
I don't use markdown very much, my code indent is not a problem, I pasted code to stackoverflow some indentation problems, please ignore. I made a mistake when I created the data table using pymysql:
Create table failure, cause: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''JD_phone' (\n id INT (15) PRIMARY KEY,\n price VARCHAR (15) NOT NUL' at line 1") Insert data failure, cause： 'tbname'
So is my SQL statement wrong, and how do I write the remaining decimal point in the SQL statement?
Oh, I've solved it, by testing every field test,I changed the long field to longs to successfully create the data table, which should be the reason for the built-in name conflict with mysql.
The table is successful and the name is JD_phone