使用Python与PYMySQL创建SQL表时出现MySQL 1064语法错误求助

代码示例 #

import pymysql
conn = pymysql.connect(
    host = 'database-1.czswegfdjhpn.us-east-1.rds.amazonaws.com',
    port = int(3306),
    user = 'admin',
    password = '',
    cursorclass=pymysql.cursors.DictCursor
)
cursor = conn.cursor()
print('connected succussfully')
cursor.execute('create database covid_19')
cursor.execute('use covid_19')

#creat table
sql_query = '''
     CREATE TABLE covid_19_world_cases_deaths_testing (
    iso_code varchar, continent varchar, location varchar, date varchar, total_cases float, \ 
    new_cases float, new_cases_smoothed float, total_deaths float, new_deaths float,\
    new_deaths_smoothed float, total_cases_per_million float, new_cases_per_million float,\
    new_cases_smoothed_per_million float, total_deaths_per_million float, new_deaths_per_million float,\
    new_deaths_smoothed_per_million float, reproduction_rate float, icu_patients float,\ 
    icu_patients_per_million float, hosp_patients float, hosp_patients_per_million float,\
    weekly_icu_admissions float, weekly_icu_admissions_per_million float, weekly_hosp_admissions float,\
    weekly_hosp_admissions_per_million float, total_tests float, new_tests float, total_tests_per_thousand float,\ 
    new_tests_per_thousand float, new_tests_smoothed float, new_tests_smoothed_per_thousand float, positive_rate float,\ 
    tests_per_case float, tests_units varchar, total_vaccinations float, people_vaccinated float, people_fully_vaccinated float,\ 
    total_boosters float, new_vaccinations float, new_vaccinations_smoothed float, total_vaccinations_per_hundred float,\ 
    people_vaccinated_per_hundred float, people_fully_vaccinated_per_hundred float, total_boosters_per_hundred float,\
    new_vaccinations_smoothed_per_million float, new_people_vaccinated_smoothed float, new_people_vaccinated_smoothed_per_hundred float,\
    stringency_index float, population float, population_density float, median_age float, aged_65_older float,\ 
    aged_70_older float, gdp_per_capita float, extreme_poverty float, cardiovasc_death_rate float, diabetes_prevalence float,\
    female_smokers float, male_smokers float, handwashing_facilities float, hospital_beds_per_thousand float, life_expectancy float,\
    human_development_index float, excess_mortality_cumulative_absolute float, excess_mortality_cumulative float, excess_mortality float,\ 
    excess_mortality_cumulative_per_million float
)
'''

cursor.execute(sql_query)

报错信息 #

ProgrammingError: (1064, "SQL语法有误；请查看与你的MySQL服务器版本对应的手册，找到在', continent varchar, location varchar, date varchar, total_cases float, \ \n n'附近使用的正确语法")

解决方法 #

问题出在MySQL的varchar类型定义上——MySQL要求必须为varchar指定最大长度，不能只写varchar关键字。你需要给每个varchar类型的字段加上长度限制，比如varchar(255)（可根据实际数据长度调整）。

修改后的SQL创建语句示例（仅展示开头部分，其余字段同理修改）：

CREATE TABLE covid_19_world_cases_deaths_testing (
    iso_code varchar(255), continent varchar(255), location varchar(255), date varchar(50), total_cases float, 
    new_cases float, new_cases_smoothed float, total_deaths float, new_deaths float,
    -- 其余字段按同样规则修改...
)

把所有varchar字段都补充长度后，重新执行代码即可成功创建表。

内容的提问来源于stack exchange，提问作者Katherine Shi