Import SQLite File into SQL Server with Python and ChatGPT

Problems appear naturally sometimes. When I would like to use SQL Server to analyse a dataset coming from Kaggle, this dataset is an SQLite file containing 6 tables. My work became how to import SQLite files into SQL Server. I want to introduce the most efficient way to learn coding with ChatGPT this time. Some points are disclosed here: structured questions result in structured answers, comparison and analogy help comprehension, find the easy material and so on.

DataSource: Kaggle 18,393 Pitchfork Reviews.

1. How to ask ChatGPT with prompt engineering:

• Give a context

• Clear purposes for the final results

• Decide how the answer is structured

Here is my example:

I have an SQLite file locally, it contains 6 tables. I would like to extract these tables and import them into SQL Server automatically. Could you please teach me how to achieve it with Python step by step?

Let me divide it into my key points:

• Context: I have an SQLite file locally, it contains 6 tables.

• Purposeful results: I would like to extract these tables and import them into SQL Server automatically.

• Structure desired answers: how to achieve it with Python step by step

Here are the steps that ChatGPT answered:

2.1 The first thing is to understand the two libraries, sqlite3 and pyodbc.

sqlite3 is to manipulate sqlite files with Python, pyodbc is to control SQL Server with Python.

I did not read the official documentation (sqlite3 and pyodbc ) completely, what I need to do is to know what work these libraries can do, that is enough.

I also found videos on YouTube (feeCodeCamp.org's Channel: SQLite Databases With Python and Jie Jenn's Channel: How to Connect To SQL Server in Python ) and in the process, sqlite3 extracts data (table names, column names and datatypes) and pyodbc creates tables and inserts data.

2.2 The second thing is to understand the cursor function.

The cursor() appears frequently in the process, so it deserves to learn deeply.

I found an amazing image coming from Codecademy and it has a comprehensive tutorial about Database Operations on Codecademy.

Do not need to spend time on obscure material, find vivid material to read.

• execute() provides an area to run SQL statements in Python

• fetchall() pulls data from a SQLite file

• commit() to make changes to the database

• close() to close the database connection

2.3 Have some conversations with ChatGPT if I have confusion about one point

For example, I did not understand why in the loop, it can iterate over each table using

for table_name in table_names:
       table_name = table_name[0]

The conversation is

I have some ideas about how it worked, but I did not understand why it can iterate these tables one by one. Then I double-checked:

Suddenly, I recalled the principle of for loop and still remember an animation where there is a line of people coming into a room one by one and understood that during for loop, the table_name would be assigned to each element one by one.

I did not find it online so I draw it coming from my memory:

I comprehended it and moved on to the next confusion:

what is PRAGMA?

To understand it, I used an analogy to help me understand it, as in SQL Server, INFORMATION_SCHEMA.COLUMNS is to extract column information. I could easily understand how it works. The efficient way is to compare them.

I figured out all my confusion and made sure I understood all codes.

3. Go back to the codes that ChatGPT provided and test it

I used VS Code to run and replaced the route of the SQLite database file, DRIVER, SERVER, and DATABASE for the connection.

I found that there was an issue in step 5, that was the datatypes are not right, and not every column is VARCHAR().

I told ChatGPT: During step 5, the datatypes should correspond to each column, could you please extract the datatype from SQLite and use it automatically when creating tables in SQL Server?

• Give a context: in step 5

• Clear purpose: extract datatype and use it when creating tables

ChatGPT gave me an updated code:

Now, I have gotten enough codes for me to achieve my goal, I put everything together and got this in my SQL Server:

4. Final Words

What I would like to show is how to learn code quickly by getting your hands dirty, and focusing on the final result that you want to achieve rather than trying to understand everything (I feel overwhelmed when facing all kinds of IT terms). What I can do is make the small step every day and I know that I will arrive somewhere I wish I could.

P.S. Make sure you understand every function using this method, otherwise, you will not be able to debug if there is something wrong. Good Luck :)