Q1. My database contains 3 columns: time, value A and value B. The time data is written in the form 00:00:00 and the increment is 1 minute. When I try to import data ... cursor.execute (f"SELECT * FROM trffc_int_data.{i};") instead getting (00:00:00, A, B), I get (datetime.timedelta(0), 7, 2), (datetime.timedelta(seconds=60), 8, 5), .....
I suppose Python doesn't convert the time right. Any suggestions?
Q2. I have an initial database with the data mentioned above. I need to get the data from the initial database, convert it, and save it to another database. I'm stuck at a point where data should be saved to a new table. Here are the sections of the code...
# Creating new DB
NewDB = input(" :: Enter the Database name : ")
sqlsynt = f"CREATE DATABASE IF NOT EXISTS {NewDB}"
cursor.execute(sqlsynt,NewDB)
stdb.commit()
# Creating table and writing the data
cursor.execute (f"USE {NewDB}")
sqlsynt = f"CREATE TABLE {dayinweek} (time TIME, Vehicles INT(3), Pedestrians INT(3))"
cursor.execute (sqlsynt, NewDB, dayinweek)
#stdb.commit()
sqlsyntax = f"INSERT INTO {NewDB}.{dayinweek} (time, Vehicles, Pedestrians) VALUES (%s, %s, %s)"
cursor.executemany(sqlsyntax, temp_list_day)
The program stucks on the last line saying that there is no table 1 in NewDB!
mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'test001.1' doesn't exist
What's wrong with the code? Maybe the problem is in mixing f and %s formating?
Thanks in advance
If I am followin this correctly, you are creating a table called 1. Digit-only identifiers are not allowed in MySQL, unless the identifier is quoted, as explained in the documentation.
Identifiers may begin with a digit but unless quoted may not consist solely of digits.
Your create table statement did fail, but you did not notice that error until you tried to insert.
You could quote the table name, using backticks:
CREATE TABLE `{dayinweek}` (time TIME, Vehicles INT(3), Pedestrians INT(3))
And then:
INSERT INTO `{NewDB}`.`{dayinweek}` (time, Vehicles, Pedestrians) VALUES (%s, %s, %s)
Quoting the database name may also be a good idea: the same rules apply as for table names (and this is user input to start with).
But overall, changing the table name seems like a better option, as this makes for cleaner code: how about something like table1 for example - or better yet, a table name that is more expressive on what kind of data it contains, such as customer1, or sales1.
Note: your code is open to SQL injection, as you are passing user input directly to the database in a create database statement. Obviously such information cannot be parameterized, however I would still recommend performing a minimal sanity check on application side beforehand.