0

EDIT: I found out that my script for inserting data was deleting zeros that are the first digit. Still don't know why this happens but my problem is solved for now.

I'm trying to send a query via mysql-connector to my database, but I keep getting errors. I can't manage to execute without single quotes (') because it returns syntax errors or mysql errors. I seem to have to use a tupel, but this comes with the disadvantage of single quotes.

mycursor.execute("CREATE TABLE IF NOT EXISTS rki (
    ObjectId INT(255),
    IdBundesland INT(255),
    Bundesland VARCHAR(255),
    Landkreis VARCHAR(255),
    Altersgruppe VARCHAR(255),
    Geschlecht VARCHAR(255),
    AnzahlFall INT(255),
    AnzahlTodesfall INT(255),
    Meldedatum VARCHAR(255),
    IdLandkreis VARCHAR(255),
    Datenstand VARCHAR(255),
    NeuerFall INT(255),
    NeuerTodesfall INT(255),
    Refdatum VARCHAR(255),
    NeuGenesen INT(255),
    AnzahlGenesen INT(255),
    IstErkrankungsbeginn INT(255),
    Altersgruppe2 VARCHAR(255)
)")
lk = "05366"
mycursor = mydb.cursor()
query = ('SELECT Landkreis FROM rki WHERE IdLandkreis = %s')
mycursor.execute(query, (lk,))
print(mycursor.statement)
kreisname = mycursor.fetchone()[0]

As you can see in the pictures executing with '' does not work, while a query without works like a charm. Is there any way to do this in python?

Not Working Working

Rudolf
  • 1
  • 1
  • Please show also the `CREATE TABLE` because it works just fine,. mysql converts it. see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=50c762907304023a92f61124fc5b98a4 – nbk Mar 03 '21 at 21:57
  • still no problem https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a385e1b8d5c494c7217043eed28643a5 – nbk Mar 03 '21 at 22:02
  • @nbk What else could be the reason? Im regenerating the database... – Rudolf Mar 03 '21 at 22:05
  • @nbk Ok I found out what the error was. When creating the database the zeros in front get lost, but I don't know why since I use varchar – Rudolf Mar 03 '21 at 22:23
  • i testd this on ypthon 3.92 and had no problems you test the input or aitomatic padding – nbk Mar 03 '21 at 22:27
  • @nbk I use pandas reading a csv which seems to be the reason the zeros are lost – Rudolf Mar 04 '21 at 07:01
  • see for example here https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas – nbk Mar 04 '21 at 08:35
  • @nbk Got it working now. But what about statements like these: `SELECT DISTINCT 'idLandkreis', 'Landkreis' FROM rki WHERE 'Landkreis' LIKE '%Aachen%'` These don't work. I need to remove the single quotes from the first three variables – Rudolf Mar 04 '21 at 08:46
  • colmns have to use backtocks or else mysql thinks it is a string see https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql you should take the time and learn much more about you r languages – nbk Mar 04 '21 at 09:03
  • @nbk Yeah that's what I'm doing right know. Thanks for your help. – Rudolf Mar 04 '21 at 09:07

1 Answers1

0
query = """SELECT Landkreis FROM rki WHERE IdLandkreis = '{}'""".format(lk)

Then pass that query to your execute. You don't need to worry about also passing the parameter as it's taken care of already.

nbk
  • 45,398
  • 8
  • 30
  • 47
acrobat
  • 812
  • 4
  • 7