I am receiving an error when trying to write data to a database table when using a variable for the table name that I do not get when using a static name. For some reason on the line where I insert, if I insert an integer as the column values the code runs and the table is filled, however, if I try to use a string I get a SQL syntax error
我在使用表名称的变量时尝试将数据写入数据库表时收到错误,这是我在使用静态名称时未获得的。由于某种原因我插入的行,如果我插入一个整数作为列值代码运行和表填充,但是,如果我尝试使用字符串我得到一个SQL语法错误
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS %s' %data[1])
sql ="""CREATE TABLE %s (IP TEXT, AVAILIBILITY INT)""" %data[1]
cursor.execute(sql)
for key in data[0]:
cur_ip = key.split(".")[3]
cursor.execute("""INSERT INTO %s VALUES (%s,%s)""" %(data[1],key,data[0][key]))
db.commit()
the problem is where I have %(data[1], key, data[0][key]) any ideas?
问题是我有%(数据[1],密钥,数据[0] [密钥])的任何想法?
1 个解决方案
#1
It's a little hard to analyse your problem when you don't post the actual error, and since we have to guess what your data actually is. But some general points as advise:
当您不发布实际错误时,分析您的问题有点困难,因为我们必须猜测您的数据实际是什么。但一些一般性的观点建议:
Using a dynamic table name is often not way DB-systems want to be used. Try thinking if the problem could be used by using a static table name and adding an additional key column to your table. Into that field you can put what you did now as a dynamic table name. This way the DB might be able to better optimize your queries, and your queries are less likely to get errors (no need to create extra tables on the fly for once, which is not a cheap thing to do. Also you would not have a need for dynamic DROP TABLE queries, which could be a security risk.
使用动态表名通常不是DB系统想要使用的方式。尝试通过使用静态表名称并向表中添加其他键列来考虑是否可以使用该问题。在该字段中,您可以将您现在所做的内容作为动态表名称。通过这种方式,数据库可以更好地优化您的查询,并且您的查询不太可能出错(无需动态创建额外的表一次,这不是一件便宜的事情。此外,您不会有需要动态DROP TABLE查询,这可能是一个安全风险。
So my advice to solve your problem would be to actually work around it by trying to get rid of dynamic table names altogether.
所以我解决你的问题的建议是通过试图完全摆脱动态表名来实际解决它。
Another problem you have is that you are using python string formatting and not parameters to the query itself. That is a security problem in itself (SQL-Injections), but also is the problem of your syntax error. When you use numbers, your expression evaluates to
您遇到的另一个问题是您使用的是python字符串格式,而不是查询本身的参数。这本身就是一个安全问题(SQL-Injections),但也是你的语法错误问题。使用数字时,表达式的计算结果为
INSERT INTO table_name VALUES (100, 200)
Which is valid SQL. But with strings you get
哪个是有效的SQL。但是你得到的是弦乐
INSERT INTO table_name VALUES (Some Text, some more text)
which is not valid (since you have no quotes ' around the strings.
这是无效的(因为你的字符串周围没有引号)。
To get rid of your syntax problem and of the sql-injection-problem, don't add the values to the string, pass them as a list to execute():
要摆脱语法问题和sql-injection-problem,不要将值添加到字符串中,将它们作为列表传递给execute():
cursor.execute("INSERT INTO table_name VALUES (%s,%s)", (key, data[0][key]))
If you must have a dynamic table name, put that in your query string first (e.g. with % formatting), and give the actual values for your query as parameters as above (since I cannot imagine that execute will accept the table name as a parameter).
如果你必须有一个动态表名,首先将它放在你的查询字符串中(例如使用%格式化),并将查询的实际值作为上述参数给出(因为我无法想象execute会接受表名作为参数)。
To put it in some simple sample code. Right now you are trying to do it like this:
把它放在一些简单的示例代码中。现在你正试图这样做:
# don't do this, this won't even work!
table_name = 'some_table'
user_name = 'Peter Smith'
user_age = 47
query = "INSERT INTO %s VALUES (%s, %s)" % (table_name, user_name, user_age)
cursor.execute(query)
That creates query
这会创建查询
INSERT INTO some_table VALUES (Peter Smith, 100)
Which cannot work, because of the unquoted string. So you needed to do:
哪个不能正常工作,因为没有引用字符串。所以你需要这样做:
# DON'T DO THIS, it's bad!
query = "INSERT INTO %s VALUES ('%s', %s)" % (table_name, user_name, user_age)
That's not a good idea, because you need to know where to put quotes and where not (which you will mess up at some point). Even worse, imagine a user named named Connor O'Neal. You would get a syntax error:
这不是一个好主意,因为你需要知道在哪里放置引号而哪些不是(在某些时候你会搞砸)。更糟糕的是,想象一个名为Connor O'Neal的用户。你会得到一个语法错误:
INSERT INTO some_table VALUES ('Connor O'Neal', 100)
(This is also the way sql-injections are used to crush your system / steal your data). So you would also need to take care of escaping the values that are strings. Getting more complicated.
(这也是sql注入用于粉碎系统/窃取数据的方式)。所以你还需要注意转义字符串的值。变得更复杂。
Leave those problems to python and mysql, by passing the date (not the table name) as arguments to execute!
将这些问题留给python和mysql,将日期(不是表名)作为参数传递给执行!
table_name = 'some_table'
user_name = 'Peter Smith'
user_age = 47
query = "INSERT INTO " + table_name + " VALUES (%s, %s)"
cursor.execute(query, (user_name, user_age))
This way you can even pass datetime objects directly. There are other ways to put the data than using %s, take a look at this examples http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html (that is python3 used there, I don't know which you use - but except of the print statements it should work with python2 as well, I think).
这样您甚至可以直接传递datetime对象。除了使用%s之外,还有其他方法来放置数据,请查看此示例http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html(即是python3在那里使用,我不知道你使用哪个 - 但除了print语句它应该与python2一起工作,我认为)。
#1
It's a little hard to analyse your problem when you don't post the actual error, and since we have to guess what your data actually is. But some general points as advise:
当您不发布实际错误时,分析您的问题有点困难,因为我们必须猜测您的数据实际是什么。但一些一般性的观点建议:
Using a dynamic table name is often not way DB-systems want to be used. Try thinking if the problem could be used by using a static table name and adding an additional key column to your table. Into that field you can put what you did now as a dynamic table name. This way the DB might be able to better optimize your queries, and your queries are less likely to get errors (no need to create extra tables on the fly for once, which is not a cheap thing to do. Also you would not have a need for dynamic DROP TABLE queries, which could be a security risk.
使用动态表名通常不是DB系统想要使用的方式。尝试通过使用静态表名称并向表中添加其他键列来考虑是否可以使用该问题。在该字段中,您可以将您现在所做的内容作为动态表名称。通过这种方式,数据库可以更好地优化您的查询,并且您的查询不太可能出错(无需动态创建额外的表一次,这不是一件便宜的事情。此外,您不会有需要动态DROP TABLE查询,这可能是一个安全风险。
So my advice to solve your problem would be to actually work around it by trying to get rid of dynamic table names altogether.
所以我解决你的问题的建议是通过试图完全摆脱动态表名来实际解决它。
Another problem you have is that you are using python string formatting and not parameters to the query itself. That is a security problem in itself (SQL-Injections), but also is the problem of your syntax error. When you use numbers, your expression evaluates to
您遇到的另一个问题是您使用的是python字符串格式,而不是查询本身的参数。这本身就是一个安全问题(SQL-Injections),但也是你的语法错误问题。使用数字时,表达式的计算结果为
INSERT INTO table_name VALUES (100, 200)
Which is valid SQL. But with strings you get
哪个是有效的SQL。但是你得到的是弦乐
INSERT INTO table_name VALUES (Some Text, some more text)
which is not valid (since you have no quotes ' around the strings.
这是无效的(因为你的字符串周围没有引号)。
To get rid of your syntax problem and of the sql-injection-problem, don't add the values to the string, pass them as a list to execute():
要摆脱语法问题和sql-injection-problem,不要将值添加到字符串中,将它们作为列表传递给execute():
cursor.execute("INSERT INTO table_name VALUES (%s,%s)", (key, data[0][key]))
If you must have a dynamic table name, put that in your query string first (e.g. with % formatting), and give the actual values for your query as parameters as above (since I cannot imagine that execute will accept the table name as a parameter).
如果你必须有一个动态表名,首先将它放在你的查询字符串中(例如使用%格式化),并将查询的实际值作为上述参数给出(因为我无法想象execute会接受表名作为参数)。
To put it in some simple sample code. Right now you are trying to do it like this:
把它放在一些简单的示例代码中。现在你正试图这样做:
# don't do this, this won't even work!
table_name = 'some_table'
user_name = 'Peter Smith'
user_age = 47
query = "INSERT INTO %s VALUES (%s, %s)" % (table_name, user_name, user_age)
cursor.execute(query)
That creates query
这会创建查询
INSERT INTO some_table VALUES (Peter Smith, 100)
Which cannot work, because of the unquoted string. So you needed to do:
哪个不能正常工作,因为没有引用字符串。所以你需要这样做:
# DON'T DO THIS, it's bad!
query = "INSERT INTO %s VALUES ('%s', %s)" % (table_name, user_name, user_age)
That's not a good idea, because you need to know where to put quotes and where not (which you will mess up at some point). Even worse, imagine a user named named Connor O'Neal. You would get a syntax error:
这不是一个好主意,因为你需要知道在哪里放置引号而哪些不是(在某些时候你会搞砸)。更糟糕的是,想象一个名为Connor O'Neal的用户。你会得到一个语法错误:
INSERT INTO some_table VALUES ('Connor O'Neal', 100)
(This is also the way sql-injections are used to crush your system / steal your data). So you would also need to take care of escaping the values that are strings. Getting more complicated.
(这也是sql注入用于粉碎系统/窃取数据的方式)。所以你还需要注意转义字符串的值。变得更复杂。
Leave those problems to python and mysql, by passing the date (not the table name) as arguments to execute!
将这些问题留给python和mysql,将日期(不是表名)作为参数传递给执行!
table_name = 'some_table'
user_name = 'Peter Smith'
user_age = 47
query = "INSERT INTO " + table_name + " VALUES (%s, %s)"
cursor.execute(query, (user_name, user_age))
This way you can even pass datetime objects directly. There are other ways to put the data than using %s, take a look at this examples http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html (that is python3 used there, I don't know which you use - but except of the print statements it should work with python2 as well, I think).
这样您甚至可以直接传递datetime对象。除了使用%s之外,还有其他方法来放置数据,请查看此示例http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html(即是python3在那里使用,我不知道你使用哪个 - 但除了print语句它应该与python2一起工作,我认为)。