Greetings all. I am learning Python on my own, and getting reacquainted with MySQL.

My current project is listing all the combinations of a lottery. I have figured this out and have now figured out how to create a ".CSV" file.

My next step is to import the .csv file into MySQL as a table. No problem.

I know I can populate the database through a Python script (this) , but this script includes the string data within the Python script. My goal is to take my script and have it populate thousands of rows of data directly into the table without an extra import step. I know the file size will be large.

Any help?

Maybe with the revolver, even a bit about snakes, other than that, I draw a blank.

This site linked below seems a good place to start, if you haven’t been there.



https://www.python.org/about/gettingstarted/

It isn't clear to me what you are having problems with. But basically you have to make a connection to the db and then enter values into the table of interest. Here is an example.

http://www.mysqltutorial.org/python-mysql-insert/

and another

http://thepythonguru.com/inserting-rows/

quote:

Originally posted by Chance:
It isn't clear to me what you are having problems with. But basically you have to make a connection to the db and then enter values into the table of interest. Here is an example.

http://www.mysqltutorial.org/python-mysql-insert/

and another

http://thepythonguru.com/inserting-rows/

Yeah, was afraid I wouldn't be able to explain it sufficiently.

So let me try this:

Python can create the data.

MySQL can hold the data.

Data has not been created yet.

I want Python to create the data while, at the same time, populating that data into a database.


Separately, I can print a file of the combinations of numbers using Python. I also know how to import those numbers into a table after creating them.

What I want is to create the number combinations and place them in a table at the same time.


That help?

Create a dictionary or a list to store the values. Then import the dictionary or list into MySQL. I prefer SQLite myself.

Not sure if that satisfies your requirement of "at the same time", but that's probably how I'd do it. 2 steps.

quote:

Originally posted by 1967Goat:
Create a dictionary or a list to store the values. Then import the dictionary or list into MySQL. I prefer SQLite myself.

Not sure if that satisfies your requirement of "at the same time", but that's probably how I'd do it. 2 steps.

What I'm trying to do is one step. Thanks goat.

quote:

Originally posted by craigcpa:

quote:

Originally posted by Chance:
It isn't clear to me what you are having problems with. But basically you have to make a connection to the db and then enter values into the table of interest. Here is an example.

http://www.mysqltutorial.org/python-mysql-insert/

and another

http://thepythonguru.com/inserting-rows/

Yeah, was afraid I wouldn't be able to explain it sufficiently.

So let me try this:

Python can create the data.

MySQL can hold the data.

Data has not been created yet.

I want Python to create the data while, at the same time, populating that data into a database.


Separately, I can print a file of the combinations of numbers using Python. I also know how to import those numbers into a table after creating them.

What I want is to create the number combinations and place them in a table at the same time.


That help?

Did the links I posted seem to fit what you are trying to do?

Remember, a key to Python is to keep everything "Pythonic". Python is all about keeping it simple. 2 steps would probably be much cleaner and easier to understand. Complexity is bad, simple is good.

I am not familiar with Python but if I understand you want this to happen.

1. Python program creates data
2. Python program inserts data into a database table

I would think a simple SQL insert statement buried in the Python code should work.

quote:

Originally posted by daikyu:
I am not familiar with Python but if I understand you want this to happen.

1. Python program creates data
2. Python program inserts data into a database table

I would think a simple SQL insert statement buried in the Python code should work.

Yes. This is succinctly what I want.

Chance, the examples in the links you provided are close to what I want (inserting multiple rows), but the data inserted is provided in the code. As daikyu succinctly stated, is there a way to bury the insert code within the Python "create" function?

quote:

Originally posted by craigcpa:

quote:

Originally posted by daikyu:
I am not familiar with Python but if I understand you want this to happen.

1. Python program creates data
2. Python program inserts data into a database table

I would think a simple SQL insert statement buried in the Python code should work.

Yes. This is succinctly what I want.

Chance, the examples in the links you provided are close to what I want (inserting multiple rows), but the data inserted is provided in the code. As daikyu succinctly stated, is there a way to bury the insert code within the Python "create" function?

I am probably missing something about what you want to do.

As noted above Python is a programming language - it can create data. MySql is a database - it houses data. To use Python to create data to be stored in MySQL the two need to talk to each other.

You can have them talk back and forth at each iteration of your Python code. Which is just a slight extension of the first example here:

http://www.mysqltutorial.org/python-mysql-insert/

It just inserts one row. Take the functionality in the example and put that that in a function and call it at each iteration.

Or you can have the Python code do its full run of iterations and then send it to the DB in one go - adding multiple rows.

The second example.

IMO multiple communications is inefficient. Create what you want in Python and then have it place that into MySQL.

quote:

Originally posted by Chance:
Or you can have the Python code do its full run of iterations and then send it to the DB in one go - adding multiple rows.

The second example.

IMO multiple communications is inefficient. Create what you want in Python and then have it place that into MySQL.

Yep. This is what I want. So, reading I do. Thanks Chance (and everyone) for the links and suggestions.

I think I follow what you're trying to do and before you get too far down this path, I'd suggest you step back and go take a look at object oriented design.

Most programming languages folks are using today and heavily object oriented and they're that way for a reason. From a future maintenance standpoint we want to be able to make changes to objects and methods without breaking (or at least substantially impacting) existing functionality - this is especially important the larger your code base becomes and the more people you have working on/in it.

You have two different things you're trying to do: create data and store created data in a DB system. From a program design perspective we want these two things to be distinct. You should have a class with a series of methods which defines how you create your data. You should also have a class (or class extension) which defines how your code interacts with the DB system to create/delete/modify table data. We want them to be separate because if something about one needs to change, we want to be able to change it without impacting the other.

What you could do is create a class with a series of methods which generates your data. Create a second class with a series of methods which interacts with your DB (create/modify/delete). At this point you have two choices: create a set of container code which calls the data generation method(s) and captures that output in a dictionary/array/custom data structure that temporarily makes the created data persist within the scope of the program, then call your DB upload method either passing it the entire data structure or call it from inside a loop that's iterating over your data structure. Otherwise you could create an instance of your DB interaction class inside the data create class and pass it the data as soon as it has been created.

For people who are new to programming, this all sounds very tedious - especially when working on very small projects that are just for your own consumption. When you're in a large code base with multiple other programmers working alongside you, without this kind of separation you very much run the risk of stepping on toes and breaking things you may not even have touched. Better to learn how to do it right the first time.