Python - Printing output into a SQL Table-CodePudding

I have a python script that can extract the metadata from pictures in a directory. As of right now they go into a text file in table format and the current output looks like this:

Filename                        |File Size(In Bytes)            |File Type                   |Image DPI                    |Image Height                  |Image Width                 |Image Format                |Image Mode                  |Image Frames                |
|X01CJ0050.JPG                  |1567529                        |.JPG                           |(300.0, 300.0)                 |400                            |600                            |JPEG                           |RGB                            |1                               |
|X01CJ0051.JPG                  |1567607                        |.JPG                           |(300.0, 300.0)                 |400                            |600                            |JPEG                           |RGB                            |1                               |
|X01CJ0072.JPG                  |1027986                        |.JPG                           |(72.0, 72.0)                   |400                            |600                            |JPEG                           |RGB                            |1                               |
|X01CJ0074.JPG                  |306064                         |.JPG                           |(72.0, 72.0)                   |400                            |600                            |JPEG                           |RGB                            |1

Here is the current script I have:

import json
import pathlib
from PIL import Image
from PIL.ExifTags import TAGS
import os
import os.path
import PIL
from pandas import json_normalize
import sqlalchemy
import pandas as pd

PIL.Image.MAX_IMAGE_PIXELS = 384000000

rootdir = r

newfile = newfile = open('meta.txt', 'w')
newfile.write("Filename                        |File Size(In Bytes)            |File     Type                   |Image DPI                    |Image Height                  |Image Width                 |Image Format                |Image Mode                  |Image Frames                |\n")
for file in os.listdir(rootdir):
    try:
        # read the image data using PIL
        image = Image.open(os.path.join(rootdir, file))

        # extract other basic metadata
        info_dict = {
            "Filename": os.path.basename(image.filename),
            "File Size": os.path.getsize(image.filename),
            "File Extension": pathlib.Path(image.filename).suffix,
            "Image DPI": image.info['dpi'],
            "Image Height": image.height,
            "Image Width": image.width,
            "Image Format": image.format,
            "Image Mode": image.mode,
            "Frames in Image": getattr(image, "n_frames", 1)
        }

        line = ""
        for label, value in info_dict.items():
            line  = f"|{str(value):<30} "
        line  = " |"
        newfile.write(line   '\n')
    except:
        # read the image data using PIL
        image = Image.open(os.path.join(rootdir, file))

        # extract other basic metadata
        info_dict = {
            "Filename": os.path.basename(image.filename),
            "Image Height": image.height,
            "Image Width": image.width,
            "Image Format": image.format,
            "Image Mode": image.mode,
            "Frames in Image": getattr(image, "n_frames", 1)
        }

        line = ""
        for label, value in info_dict.items():
            line  = f"|{str(value):<30} "
        line  = " |"
        newfile.write(line   '\n')
    
#Connect to the database
testDBCon = sqlalchemy.create_engine('mssql pyodbc://SRVISCDB,58837/testDB? 
driver=SQL Server Native Client 11.0')

#Choose what query to select a column from
query = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"

The script opens the directory and for each photo it takes the filename, size, type, height, width, DPI, mode and Frames. And as I stated it prints into a text file. Instead of going to a print file I would like for the output to be put into a SQL table that is already created. I have some lines of code that connects me to the database table and those are:

#Connect to the database
testDBCon = sqlalchemy.create_engine('mssql pyodbc://SRVISCDB,58837/testDB? 
driver=SQL Server Native Client 11.0')

#Choose what query to select a column from
query = "SELECT * FROM testDB.dbo.SuspensiaImageDetails;"

So what I would like to know is, How do i print the output to the table?

CodePudding user response：

You are almost there. You have created an engine and now you need to create a connection, open a cursor and perform the query you want.

connection = testDBCon.connect()
cur = conn.cursor()

cur.execute(your sql) ##INSERT if you want to add data to an existing table, CREATE if it is a new table or SELECT if you just want to get data from db

conn.commit() ##to commit changes to database

CodePudding user response：

Assuming you already have the table created according to the convention followed while printing the data, a simple INSERT query should suffice.

That being said, instead of line = " |" you would have to use line = ","

Following your convention, it should go something like :

query = "INSERT INTO testDB.dbo.SuspensiaImageDetails VALUES ("
query  = line
query  = ");"

You can't Print stuff into a mysql db per se.