好吧,那么我将以“我对python还不熟悉”作为序言。我正在使用pycharm运行我正在构建的这个程序,以便转储到数据库(本地托管的mysql)。
不幸的是,我在用python将数据导入数据库时遇到了问题,但是我能够通过phpmyadminwebgui在sql中插入数据。我一定错过了什么明显的东西。我正在为pycharm使用“mysql.connector”插件。以下是相关代码。
那么,在我开始导入模块的时候?像这样;
import mysql.connector
这个代码在一对while循环中引用;
cnx = mysql.connector.connect(host ='localhost', user = 'root', passwd= '', db='weather_test01')
c = cnx.cursor()
c.execute("INSERT INTO 'stations' ('dtime', 'tmp', 'apptmp', 'dewpoint', 'relhum', 'delta_t', 'wind_dir', 'wind_spd_kmh', 'wind_gust_kmh', 'wind_spd_kts', 'wind_gust_kts', 'press_qnh', 'press_msl', 'rainsince9am') VALUES (dtime1, tmp1, apptmp1, dewpoint1, relhum1, delta_t1, wind_dir1, wind_spd_kmh1, wind_gust_kmh1, wind_spd_kts1, wind_gust_kts1, press_qnh1, press_msl1, rainsince9am1)")
cnx.commit()
请不要因为我的写作方式而生吞我,我是新来的:)
其他重要因素;
我可以让它多次输入数据(但它作为空值传入sql)。我猜我传递的数字格式不对?或者与数据库排序规则有关?)
我还尝试了许多不同的pycharm插件(它们似乎都以类似的方式实现)。
我试过在插入的变量名上加引号和不加引号,我试了这么多,我都快不知所措了。
至于错误代码,这是它目前的样子;
C:\Users\logge\PycharmProjects\untitled3\venv\Scripts\python.exe C:/Users/logge/PycharmProjects/untitled3/main.py
Traceback (most recent call last):
File "C:/Users/logge/PycharmProjects/untitled3/main.py", line 111, in <module>
weather(1)
File "C:/Users/logge/PycharmProjects/untitled3/main.py", line 101, in weather
c.execute("INSERT INTO 'stations' ('dtime', 'tmp', 'apptmp', 'dewpoint', 'relhum', 'delta_t', 'wind_dir', 'wind_spd_kmh', 'wind_gust_kmh', 'wind_spd_kts', 'wind_gust_kts', 'press_qnh', 'press_msl', 'rainsince9am') VALUES (dtime1, tmp1, apptmp1, dewpoint1, relhum1, delta_t1, wind_dir1, wind_spd_kmh1, wind_gust_kmh1, wind_spd_kts1, wind_gust_kts1, press_qnh1, press_msl1, rainsince9am1)")
File "C:\Users\logge\PycharmProjects\untitled3\venv\lib\site-packages\mysql\connector\cursor.py", line 561, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\logge\PycharmProjects\untitled3\venv\lib\site-packages\mysql\connector\connection.py", line 525, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\logge\PycharmProjects\untitled3\venv\lib\site-packages\mysql\connector\connection.py", line 427, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''stations' ('dtime', 'tmp', 'apptmp', 'dewpoint', 'relhum', 'delta_t', 'wind_dir' at line 1
Process finished with exit code 1
错误代码中提到的“101行”是指以“c.execute…”开头的行。。。在我发布的代码的第二部分插入'。
下面是我也尝试过的代码,只是想看看是否可以得到任何结果(尽管我仍然得到相同的错误):
from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector
dtime1 = 1
tmp1 = 5
apptmp1 = 2
dewpoint1 = 64
relhum1 = 3
delta_t1 = 4
wind_dir1 = 5
wind_spd_kmh1 = 6
wind_gust_kmh1 = 7
wind_spd_kts1 = 8
wind_gust_kts1 = 9
press_qnh1 = 10
press_msl1 = 11
rainsince9am1 =12
cnx = mysql.connector.connect(host ='localhost', user = 'root', passwd= '', db='weather_test01')
cursor = cnx.cursor()
tomorrow = datetime.now().date() + timedelta(days=1)
add_employee = ("INSERT INTO stations "
"(dtime, tmp, apptmp, dewpoint, relhum, delta_t, wind_dir, wind_spd_kmh, wind_gust_kmh, wind_spd_kts, wind_gust_kts, press_qnh, press_msl, rainsince9am) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,)")
data_employee = (dtime1, tmp1, apptmp1, dewpoint1, relhum1, delta_t1, wind_dir1, wind_spd_kmh1, wind_gust_kmh1, wind_spd_kts1, wind_gust_kts1, press_qnh1, press_msl1, rainsince9am1)
# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid
# Make sure data is committed to the database
cnx.commit()
cursor.close()
cnx.close()
任何帮助或建议将不胜感激。我很高兴张贴整个代码,如果需要也。我只是不想把整件事搞得一团糟。
2条答案
按热度按时间a0x5cqrl1#
我发现了你的错误。您已将输入数据引用为字符串(%s)。但是你在输入整数(数字)。
如果您想把数字作为字符串,在第一块代码(您的)中,您需要输入
str(variable)
而不仅仅是variable
. 在第二段代码(从mysql复制)中,可以将变量声明为variable = "0000"
带着引号。如果要在代码中处理数字,请将表结构更改为
INTEGER
或者DOUBLE
而不是VARCHAR
或者CHAR
.我假设表存在,并且所有字段都存在,因为连接到数据库并查找表时没有错误。
yizd12fk2#
下面的代码可以将sql数据转换成nosql数据。对于sql,我使用mysql服务器,nosql使用mongodb。这是密码